Can the OUTPUT clause create a table?

Question

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?

score 8 · Accepted Answer · answered Mar 22 '13 at 18:35

8

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.

answered Mar 22 '13 at 18:35

Aaron Bertrand

181,950
28
405
624

score 6 · Answer 2 · answered May 02 '16 at 20:24

Aaron Bertrand is right. The documentation for the OUTPUT clause is not explicit in saying that a table cannot be created with its use. I've run into the same issue. What I ended up doing was using two statements:

--This creates the table with the needed columns without inserting any data.
SELECT TOP 0 [myColumn1], [myColumn2], ...etc
INTO [myNewTable]
FROM [myTable]

--This updates the records and inserts the updated data into the new table.
UPDATE [myTable]
SET [myColumn1] = 'Some Value',
    [myColumn2] = 'Some Other Value', ...etc
OUTPUT 
    [myColumn1], [myColumn2], ...etc
    INTO [myNewTable]
FROM [myTable] ...etc.

I know this answer is coming a bit late, but I hope it helps anybody else who is running into similar issues. Happy scripting!

score -3 · Answer 3 · answered Mar 25 '13 at 04:20

Use a subquery.

Into an existing table:

insert into dbo.ExistingTable
select * 
from
    ( 
    UPDATE dbo.Table1
    SET BirthDate = b.BirthDate
    OUTPUT 
        inserted.Id, inserted.BirthDate AS New_BirthDate, 
        deleted.BirthDate AS Old_BirthDate
    FROM Table1 a
    JOIN Table2 b
        ON a.ID = b.ID
    ) as UpdatedRows

Creating a new table:

select * 
into dbo.NewTable
from
    ( 
    …
    ) as UpdatedRows

The into keyword in the output clause can, as far as I know, only be used to put the results into a table-typed local variable.

Can the OUTPUT clause create a table?

3 Answers3