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 one transaction. Now I already know how to do this by doing INSERT-SELECT-INSERT, for example:

 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; 

This is all good and perfect for small cases, for example, where you only insert maybe a few lines. But I need to insert a couple of hundred thousand lines, or maybe even a million lines, all at once. The data comes from another table, so if I only inserted it into one table, it would be simple, I just would 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 corresponding [table1_id] , how do I do it? Is it possible?

Answer 1

Try the following:

 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 maybe you could fix the following SQL statement for me? Basically, [data] in [table1] and [data] in [table2] represent two different / separate columns from [external_table]. The above statement 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 is not possible to output external columns in an insert , 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] ; 

Answer 2

I also struggled with this problem and believe that the best way is to use CURSOR .

I tried Denis solution with OUTPUT, but as he mentions, it is not possible to output external columns in the insert statement, and MERGE cannot work when you insert multiple rows of your choice.

So, I used CURSOR, for each row in the external table I did INSERT and then used @@ 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 

Answer 3

Ensure that SQL Server supports the INSERT ALL statement. Oracle already has it, 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 

Answer 4

 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; 

Answer 5

Another option is to run the two inserts separately, leaving the FK column zero, and then running the update to properly clear it.

If there is nothing natural in the two tables that correspond from one record to the other (most likely), create a temporary GUID column and fill it in your data and paste in both fields. Then you can update with the correct FK and exclude the GUID.

For example:

 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 

Answer 6

 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 request ------

 insert into #temp1(name,profession) output inserted.name,inserted.profession into #temp2 select 'Shekhar','IT' 

Answer 7

You can write a stored procedure that iterates over the transaction you proposed. The iterator will be the cursor for the table containing the source data.