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.
Advertisement
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