Sql-server – Output into temporary table

sql-server-2008sql-server-2008-r2

I am trying to create a procedure that will generate an xml document of a given table whilst updating the information in said table. My procedure does not recognise the use of the temporary table unless I use a CREATE TABLE statement (I have not tried table var's yet). Ideally I would not use the CREATE TABLE statement in case I/another developer wanted to add another column(s) in the future.

Here is my code currently:

UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp 
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

And it fails with:

Msg 208, Level 16, State 0, Line 24

Invalid object name '##temp'.

It's not the end of the world if I have to use a CREATE TABLE statement, but I would prefer not to explicitly declared it.

Notes:

  1. I know there is no error handling at the moment, I want to get this section working first.
  2. I have also tried naming the columns instead of using Inserted.*
  3. I am purposely setting ErrorReported = 0 as I am in a test environment

Best Answer

You must create a temp table with Audit.ErrorLog Columns and then get output records.

Declare @temp Table (ErrorLogID Int, ErrorReported Int, Error NVarChar(100))

UPDATE Audit.ErrorLog 
SET ErrorReported = 0 
OUTPUT inserted.* 
INTO @temp  

SELECT * 
FROM @temp  
FOR XML PATH('Error'), ROOT ('Errors')
Related Question