Output to Temporary Table in SQL Server 2005

sql-server-2005 output-clause
B.M picture B.M · Jun 9, 2011 · Viewed 22.2k times · Source

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?

Answer

marc_s picture marc_s · Jun 9, 2011

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More