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

user_5454 0
Viewed 142576

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?

insert foreign-keys tsql sql-server sql
7 Answers
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_118294610
Related