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?

• Martin Smith: There's always the OPENROWSET hack...

Answer 1

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.

Answer 2

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!

• HeyJude: The OUTPUT columns should be prefixed with inserted or deleted (depending on one needs).

Answer 3

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.

• Kenneth Fisher: This was a great idea but when I tried it out I got the following error: A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.