Output to Temporary Table in SQL Server 2005

Question

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

Answer 1

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