sql server update column to primary key in another table to prepare data for insert

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.