I need to migrate data from one old table to a new table by storing appropriate CityId instead CityName

I’m migrating data from one table to another table in SQL Server, In this process what I need to do is “I have 10 columns in old table one column is ‘CityName’ which is varchar and in the new table, I have a column ‘CityId’ which is an integer. And I have other table which has data about city id and names. I need store the appropriate cityId in new table instead of CityName. Please help me. Thanks in advance.

Answer

Below should give you an idea, you need to inner join to your look up table to achieve this.

declare @t_cities table (Id int, City nvarchar(20))

insert into @t_cities 
    (Id, City)
values
    (1, 'London'),
    (2, 'Dublin'),
    (3, 'Paris'),
    (4, 'Berlin')    

declare @t table (City nvarchar(20), SomeColumn nvarchar(10))

insert into @t
values 
    ('London', 'AaaLon'),
    ('Paris', 'BeePar'),
    ('Berlin', 'CeeBer'),
    ('London', 'DeeLon'),
    ('Dublin', 'EeeDub')

declare @finalTable table (Id int, SomeColumn nvarchar(10))

insert into @finalTable
select c.Id, t.SomeColumn
from @t t
    join @t_cities c on c.City = t.City

select * from  @finalTable

Output:

Id  SomeColumn
1   AaaLon
3   BeePar
4   CeeBer
1   DeeLon
2   EeeDub