How can I INSERT data into two tables simultaneously in SQL Server

Question

Let s say my table structure looks something like this   CREATE TABLE  dbo   table1         id   int  IDENTITY 1 1  NOT NULL       data   varchar  255  NOT NULL      CONSTRAINT  PK table1  PRIMARY KEY CLUSTERED   id  ASC     CREATE TABLE  dbo   table2         id   int  IDENTITY 1 1  NOT NULL       table1 id   int  NOT NULL       data   varchar  255  NOT NULL      CONSTRAINT  PK table2  PRIMARY KEY CLUSTERED   id  ASC      The  id  field of the first table corresponds to the  table1 id  field of the second  What I would like to do is insert data into both tables in a single transaction  Now I already know how to do this by doing INSERT-SELECT-INSERT  like this   BEGIN TRANSACTION  DECLARE  id  int   INSERT INTO  table1    data   VALUES   row 1    SELECT  id   SCOPE IDENTITY    INSERT INTO  table2    table1 id    data   VALUES   id   more of row 1    COMMIT TRANSACTION    That s all good and fine for small cases like that where you re only inserting maybe a handful of rows  But what I need to do is insert a couple hundred thousand rows  or possibly even a million rows  all at once  The data is coming from another table  so if I was only inserting it into a single table  it would be easy  I d just have to do this   INSERT INTO  table    data   SELECT  data  FROM  external table     But how would I do this and split the data into  table1  and  table2   and still update  table2  with the appropriate  table1 id  as I m doing it  Is that even possible

User · Answer

BEGIN TRANSACTION   DECLARE  tblMapping table sourceid int  destid int   INSERT INTO  table1    data    OUTPUT source id  new id Select  data  from  external table  source   INSERT INTO  table2    table1 id    data   Select map destid  source  more data   from  external table  source     inner join  tblMapping map on source id map sourceid   COMMIT TRANSACTION

User · Answer

Keep a look out for SQL Server to support the  INSERT ALL  Statement   Oracle has it already  it looks like this  SQL Cookbook    insert all   when loc in   NEW YORK    BOSTON   THEN    into dept east deptno  dname  loc  values deptno  dname  loc    when loc in   CHICAGO   THEN    into dept mid deptno  dname  loc  values deptno  dname  loc    else    into dept west deptno  dname  loc  values deptno  dname  loc  select deptno  dname  loc   from dept

User · Answer

Create table  temp1    id int identity 1 1    name varchar 50    profession varchar 50        Create table  temp2    id int identity 1 1    name varchar 50    profession varchar 50      -----main query ------  insert into  temp1 name profession   output inserted name inserted profession into  temp2  select  Shekhar   IT

User · Answer

Another option is to run the two inserts separately  leaving the FK column null  then running an update to poulate it correctly     If there is nothing natural stored within the two tables that match from one record to another  likely  then create a temporary GUID column and populate this in your data and insert to both fields   Then you can update with the proper FK and null out the GUIDs   E g      CREATE TABLE  dbo   table1          id   int  IDENTITY 1 1  NOT NULL        data   varchar  255  NOT NULL       CONSTRAINT  PK table1  PRIMARY KEY CLUSTERED   id  ASC       JoinGuid UniqueIdentifier NULL     CREATE TABLE  dbo   table2          id   int  IDENTITY 1 1  NOT NULL        table1 id   int  NULL        data   varchar  255  NOT NULL       CONSTRAINT  PK table2  PRIMARY KEY CLUSTERED   id  ASC       JoinGuid UniqueIdentifier NULL      INSERT INTO Table1      INSERT INTO Table2      UPDATE b SET table1 id   a id FROM Table1 a JOIN Table2 b on a JoinGuid   b JoinGuid WHERE b table1 id IS NULL  UPDATE Table1 SET JoinGuid   NULL UPDATE Table2 SET JoinGuid   NULL

User · Answer

You could write a stored procedure that iterates over the transaction that you have proposed  The iterator would be the cursor for the table that contains the source data

User · Answer

I was also struggling with this problem  and find that the best way is to use a CURSOR   I have tried Denis solution with OUTPUT  but as he mentiond  it s impossible to output external columns in an insert statement  and the MERGE can t work when insert multiple rows by select   So  i ve used a CURSOR  for each row in the outer table  i ve done a INSERT  then use the   IDENTITY for another INSERT   DECLARE  OuterID int  DECLARE MY CURSOR CURSOR    LOCAL STATIC READ ONLY FORWARD ONLY FOR  SELECT  ID FROM    external Table   OPEN MY CURSOR FETCH NEXT FROM MY CURSOR INTO  OuterID  WHILE   FETCH STATUS   0 BEGIN  INSERT INTO  Table     data      SELECT data     FROM      external Table  where ID    OuterID       INSERT INTO  second table   FK OuterID      VALUES  OuterID   identity       FETCH NEXT FROM MY CURSOR INTO  OuterID END CLOSE MY CURSOR DEALLOCATE MY CURSOR

User · Answer

Try this   insert into  table    data   output inserted id  inserted data into table2 select  data  from  external table    UPDATE  Re      Denis - this seems very close to what I want to do  but perhaps you could fix the following SQL statement for me  Basically the  data  in  table1  and the  data  in  table2  represent two different distinct columns from  external table   The statement you posted above only works when you want the  data  columns to be the same     INSERT INTO  table1    data    OUTPUT  inserted   id    external table   col2   INTO  table2  SELECT  col1   FROM  external table     It s impossible to output external columns in an insert statement  so I think you could do something like this  merge into  table1  as t using  external table  as s on 1 0 --modify this predicate as necessary when not matched then insert  data  values  s  col1   output inserted id  s  col2  into  table2

[sql] How can I INSERT data into two tables simultaneously in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to insert

Examples related to foreign-keys