Problem:
i have excel file need to export it in more than table into sql server database .
so after import data for temp table i need to select every primary table data and insert it.
so i need to create mapping columns for primary keys for master table to be able to insert detail table data with primary key for table.
example : i have this excel file data
i have two tables :
RegionsTable
CityTable
i need after export to create insert into these two tables .
Region Table
City Table
solution:
1- select distinct regions from temp table and insert into Region Table:
INSERT INTO [Lookups].[Regions] ([RegionId] ,[RegionNameAR] ,[RegionNameEN] ,[RegionPostalCode] ,[GeoRegionId]) select distinct [RegionId] ,[RegionNameAR] ,[RegionNameEN] ,[RegionPostalCode] ,[GeoRegionId] from dbo.Cities$2- create new column in temp table to save RegionId into .
ALTER TABLE dbo.Cities$ ADD
RegionRefID tinyint NULL
GO
update dbo.Cities$
update dbo.Cities$
set RegionRefID = r.RegionId
from dbo.Cities$ c
inner join Lookups.Regions r
on
r.RegionId=c.RegionId
and r.RegionNameAR=c.RegionNameAR
and r.RegionNameEn=c.RegionNameEn
and r.RegionPostalCode=c.RegionPostalCode
and r.GeoRegionId =c.GeoRegionId
3-update city Table with city and mapp new regionid with query to keep reference to Region Table.
INSERT INTO [Lookups].[City] ([CityId] ,[CityNameAR] ,[CityNameEN] ,[RegionId]) select Id, CityNameAr ,CityNameEn ,RegionIdNew from dbo.Cities$ GO
done.
