Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
It is possible using the technique in my answer here. Not sure if there is any other way. –  Martin Smith Feb 13 '12 at 17:12
    
Thanks. Fancy putting that in an answer :) –  Stuart Blackler Feb 15 '12 at 21:34

1 Answer 1

up vote 0 down vote accepted

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')  
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.