How to insert into two tables in one statement
Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement. Right now, I can't see a practical use, but that might change in the future. As long as I remember this blog post exists…
DECLARE @Source TABLE
(
a INT,
b INT
)
INSERT @Source
SELECT 11, 21 UNION ALL
SELECT 12, 22
DECLARE @PrimaryTarget TABLE
(
a INT
)
DECLARE @SecondaryTarget TABLE
(
b INT
)
MERGE @PrimaryTarget AS pt
USING @Source AS s ON 1 = 1
WHEN NOT MATCHED BY TARGET
THEN INSERT (
a
)
VALUES (
s.a
)
OUTPUT s.b
INTO @SecondaryTarget;
SELECT 'Source' AS TableName,
a,
b
FROM @Source
SELECT 'PrimaryTarget' AS TableName,
a
FROM @PrimaryTarget
SELECT 'SecondaryTarget' AS TableName,
b
FROM @SecondaryTarget
Legacy Comments
|
Adam Machanic
2009-07-29 |
re: How to insert into two tables in one statement I use this all the time to grab surrogate keys, changed rows for logging, etc, etc... Plenty of use cases :-) |
|
Peso
2009-07-29 |
re: How to insert into two tables in one statement Well, that explains how vacation affects my brains... :-) |
|
Bart Duncan
2009-08-02 |
re: How to insert into two tables in one statement MERGE is also great for upserts |
|
Shital kasliwal
2009-08-05 |
re: How to insert into two tables in one statement is it possible in sql 2005? or is it only possible in sql 2008? |
|
Peso
2009-08-05 |
re: How to insert into two tables in one statement Merge is a feature of SQL Server 2008. |
|
vinoth
2009-09-29 |
re: How to insert into two tables in one statement see the url and reply me http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx |
|
Amir
2010-10-06 |
re: How to insert into two tables in one statement Hello There, Thanks for this article which seems very useful to my current situation which is a bit more complicated , I would appreciate if you could help me in it, I want to have two insert in WHEN NOT MATCHED of MERGE statement to compare with your code the Second Target table's Primary Key is Foreign Key for the First Target Table; I wonder if there is any way to insert into second table first to use its generated Pkey to use in the other table. Thank you in advance |