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?
This question is related to
sql
sql-server
tsql
insert
foreign-keys
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
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.
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;
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
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
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'
Source: Stackoverflow.com