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, 'Test3…

Accepted Answer

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

SQL Server: Multiple Output Clauses

Solution

Problem