HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Can the OUTPUT clause create a table?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canthecreateoutputclausetable

Problem

I'm doing an update like this:

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID


And I want to use the OUTPUT clause to back up my changes.

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
OUTPUT 
    inserted.Id, inserted.BirthDate AS New_BirthDate, 
    deleted.BirthDate AS Old_BirthDate
    INTO OutputTable
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID


What I want to know is if there is a way for the OUTPUT clause to create the table OutputTable or do I have to make sure it already exists before running the statement?

Solution

AFAIK, the target table must exist, though the documentation is not explicit and probably should be. I can't see anything in the syntax diagram that would support any type of extension to create the output table at the same time.

Context

StackExchange Database Administrators Q#37363, answer score: 8

Revisions (0)

No revisions yet.