Output to Temporary Table in SQL Server 2005
Asked Answered
G

1

9

I am trying to use the OUTPUT clause inside a stored procedure to output to a temporary table the values of an indentity column after an INSERT.

CREATE TABLE #Test
(
    ID INT
)

INSERT INTO [TableB] OUTPUT INSERTED.ID #Test SELECT * FROM [TableA]

However, when I execute this procedure SQL Server shows me the results in a table (correctly) called Test but if I write SELECT * FROM #Test as the next statement in the stored procedure it shows me nothing. How can I efectively accomplish this?

Granvillegranvillebarker answered 9/6, 2011 at 11:35 Comment(0)
F
21

I think you're missing an INTO - try this:

CREATE TABLE #Test(ID INT)

INSERT INTO [TableB] 
    OUTPUT INSERTED.ID INTO #Test 
    SELECT * FROM [TableA]

After the list of columns to OUTPUT, add an INTO before the table name

Ferguson answered 9/6, 2011 at 11:39 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.