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.
- I know there is no error handling at the moment, I want to get this section working first.
- I have also tried naming the columns instead of using
- I am purposely setting
ErrorReported = 0as I am in a test environment