SQL Server: Multiple Output Clauses

Question

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?

[Read the MSDN documentation on the `OUTPUT` clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx) - you can output multiple rows into e.g. a temporary table (or a table variable - or directly into a target table, too!) — marc_s, Oct 26 '12 at 20:45
I've read that but may have missed what you're referring to. Outputting something liked INSERTED.* isn't really appropriate here because I only want to output the value from one inserted row, multiple times. I don't suppose you could give me an example? — SuperNES, Oct 26 '12 at 20:59

score 3 · Accepted Answer · answered Oct 26 '12 at 21:07

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...

score 0 · Answer 2 · edited Dec 16 '16 at 12:56

0

Try putting the INSERTED.PK value into a parameter, then inserting into table 2 with 3 INSERT..VALUES or 1 INSERT..SELECT statement.

edited Dec 16 '16 at 12:56

Igor Borisenko

3,806
3
34
49

answered Oct 26 '12 at 20:41

Nick Vaccaro

5,428
6
38
60

SQL Server: Multiple Output Clauses

2 Answers2

Linked