Insert into multiple tables

• CookieMonster

  SSC Veteran

  Points: 210

  More actions

  March 25, 2014 at 4:11 am

  #283761

  Hi All,

  I need to insert approximately 500k rows into sql, but on each insert i need to get the inserted row id so i can perform insert into another table.

  eg:

  Table A

  col1_ProductId | cold2_Name

  1 | Product1Name

  2 | Product2Name

  Table B

  col1_ID | col2_ProductId | Attribute

  1 | 1 | blue

  2 | 1 | small

  3 | 2 | red

  4 | 2 | large

  I'm after the most efficient way possible as the task will be repeated regularly.

  Thanks in advance

• CKX

  SSCrazy

  Points: 2519

  More actions

  March 25, 2014 at 4:28 am

  #1699778

  CookieMonster (3/25/2014)

  ---

  Hi All,

  I need to insert approximately 500k rows into sql, but on each insert i need to get the inserted row id so i can perform insert into another table.

  eg:

  Table A

  col1_ProductId | cold2_Name

  1 | Product1Name

  2 | Product2Name

  Table B

  col1_ID | col2_ProductId | Attribute

  1 | 1 | blue

  2 | 1 | small

  3 | 2 | red

  4 | 2 | large

  I'm after the most efficient way possible as the task will be repeated regularly.

  Thanks in advance

  Are you attempting to do the INSERT in one large operation or one after the other from a front end or something along those lines?

  You can use the OUTPUT Clause to fetch Identity ID's inserted in a Table.

  http://technet.microsoft.com/en-us/library/ms177564.aspx

• CookieMonster

  SSC Veteran

  Points: 210

  More actions

  March 25, 2014 at 4:50 am

  #1699789

  cheers for the reply, I will take a look at OUTPUT clause,

  I'm not sure, I'm just looking for the most efficient way, would it be quicker to do a bulk insert and then select from the to insert into other tables or is it more efficient to insert row by row with a nested insert?