0

i have two temp tables containing thousand of records. following are the table structures.

 #TmpCity

 ID | CityName

 1  |  test


 #Location
 ID |  LocationName

 1  |  abc
 2  |  xyz
 3  |  etc

i have 2 physical tables City and Location with same schema, where as Location Table contains a foreign key CityID which will be auto incremented primary key of city table. i need to add data from #tmpCity to City Table and #tmpLocation to Location Table.

i can not use INSERT INTO as for each City record inserted i have to insert location record for that city with a scopeIdentity, due to huge number of record i want to avoid row by row operation like loop or cursor. what is the best approach to achieve this.

Sohail Hameed
  • 978
  • 2
  • 9
  • 25
  • why dont you use `insert into`? just select values from temp table after `insert` command? – Badiparmagi Aug 21 '17 at 12:46
  • I'd use `OUTPUT` clause together with `MERGE` statement as I showed in this similar question https://stackoverflow.com/q/41595802/4116017 – Vladimir Baranov Aug 21 '17 at 13:16
  • Possible duplicate of [Cascade copy a row with all child rows and their child rows, etc](https://stackoverflow.com/questions/41595802/cascade-copy-a-row-with-all-child-rows-and-their-child-rows-etc) – Vladimir Baranov Aug 21 '17 at 13:17
  • I can't use insert into, as i need a auto incremented PK when i insert data from #tmpCity to City table, PK will go lo location table as a foreign key. – Sohail Hameed Aug 22 '17 at 07:40

1 Answers1

1
INSERT INTO [dbo].[City]([CityName])
Select [CityName] from [dbo].[#TmpCity]

INSERT INTO [dbo].[Location]([CityId],[LocationName])
Select (select TOP(1) tab1.[CityId]  from [dbo].[City] as tab1
join  [dbo].[#City] as tab2 on tab1.[CityName]=tab2.[CityName]
where tab2.[ID]=loc.[CityId]),loc.[LocationName] from [dbo].[#Location] as loc
Hitesh Thakor
  • 471
  • 2
  • 12
  • 1
    An expalantion of your code and some formatting would go a long way to making this a lot better answer. The formatting here makes this into a wall of text which is really difficult to read. – Sean Lange Aug 21 '17 at 13:29
  • i edit the question. right now i don't have cityID in Location table. i have to get the auto incremented PK from City Table to insert into location table. – Sohail Hameed Aug 22 '17 at 05:36
  • just try it will set FK in location table from city table – Hitesh Thakor Aug 22 '17 at 07:04