Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Wednesday, July 29, 2009 6:16 PM | Filed Under [ SQL Server 2008 Algorithms ]

Feedback

Gravatar

# 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 :-)
7/29/2009 7:05 PM | Adam Machanic
Gravatar

# re: How to insert into two tables in one statement

Well, that explains how vacation affects my brains...
:-)
7/29/2009 7:08 PM | Peso
Gravatar

# re: How to insert into two tables in one statement

MERGE is also great for upserts
8/2/2009 4:31 PM | Bart Duncan
Gravatar

# re: How to insert into two tables in one statement

is it possible in sql 2005? or is it only possible in sql 2008?
8/5/2009 6:48 AM | Shital kasliwal
Gravatar

# re: How to insert into two tables in one statement

Merge is a feature of SQL Server 2008.
8/5/2009 8:22 AM | Peso
Gravatar

# 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
9/29/2009 11:42 AM | vinoth
Gravatar

# 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
10/6/2010 11:27 PM | Amir
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET