SQL Server: Multiple Output Clauses

sql-server output-clause
SuperNES picture SuperNES · Oct 26, 2012 · Viewed 13.2k times · Source

I have two tables, Table_1 and Table_2.

Table_1 has columns PK (autoincrementing int) and Value (nchar(10)).

Table_2 has FK (int), Key (nchar(10)) and Value (nchar(10)).

That is to say, Table_1 is a table of data and Table_2 is a key-value store where one row in Table_1 may correspond to 0, 1 or more keys and values in Table_2.

I'd like to write code that programmatically builds up a query that inserts one row into Table_1 and a variable number of rows into Table_2 using the primary key from Table_1.

I can do it easy with one row:

INSERT INTO Table_1 ([Value])
OUTPUT INSERTED.PK, 'Test1Key', 'Test1Val' INTO Table_2 (FK, [Key], [Value])
VALUES ('Test')

But SQL doesn't seem to like the idea of having multiple rows. This fails:

INSERT INTO Table_1 ([Value])
OUTPUT INSERTED.PK, 'Test1Key', 'Test1Val' INTO Table_2 (FK, [Key], [Value])
OUTPUT INSERTED.PK, 'Test2Key', 'Test2Val' INTO Table_2 (FK, [Key], [Value])
OUTPUT INSERTED.PK, 'Test3Key', 'Test3Val' INTO Table_2 (FK, [Key], [Value])
VALUES ('Test')

Is there any way to make this work?

Answer

OzrenTkalcecKrznaric picture OzrenTkalcecKrznaric · Oct 26, 2012

I had to put the code in answer, in comment it looks ugly...

CREATE TABLE #Tmp(PK int, value nchar(10))

INSERT INTO Table_1 ([Value])
OUTPUT INSERTED.PK, inserted.[Value] INTO #Tmp
SELECT 'Test'

INSERT INTO Table_2 (FK, [Key], Value)
SELECT PK, 'Test1Key', 'Test1Val' FROM #Tmp
UNION ALL SELECT PK, 'Test2Key', 'Test2Val' FROM #Tmp
UNION ALL SELECT PK, 'Test3Key', 'Test3Val' FROM #Tmp

Btw, SQL Server won't let you do it all in one query without some ugly hack...

Related questions

@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@…

Read More
DELETE ... OUTPUT COUNT(DELETED.*)

I want to know how many rows were removed in a certain DELETE operation. I took the Microsoft example B which is DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621; and tried to modify it to return only the count of …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More