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?

Answer 1

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] ; 

Answer 2

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