Can the OUTPUT clause create a table?
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.
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!