I recently wrote an article for SQL Server Central about using OUTPUT. In case you aren’t aware OUTPUT is a clause that lets you output the changed data from an INSERT, UPDATE, DELETE, or MERGE statement. One of the comments on the article was a question about inserting the outputed data into a table with an IDENTITY column. Now the answer is actually pretty easy but I can see where the poster was confused. Most of the examples I’ve seen, my own included, look like this:

CREATE TABLE Source (col1 int, col2 int);
INSERT INTO Source VALUES (1,1),(1,1),(1,1),(1,1);
SELECT TOP 0 * INTO Archive FROM Source ;
GO
DELETE FROM Source
OUTPUT deleted.col1, deleted.col2
INTO Archive;
GO

But what happens if you add an identity column?

CREATE TABLE Source (id int not null identity(1,1), col1 int, col2 int);
INSERT INTO Source VALUES (1,1),(1,1),(1,1),(1,1);
SELECT TOP 0 * INTO Archive FROM Source ;
GO
DELETE FROM Source
OUTPUT deleted.id, deleted.col1, deleted.col2
INTO Archive;
GO

Msg 8101, Level 16, State 1, Line 8

An explicit value for the identity column in table ‘Archive’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

So what’s the problem? Well, to start with by using the SELECT TOP 0 INTO Archive method of creating the Archive table you’ve created the archive table with an identity column. Get rid of that identity property (you still want the column) and you don’t have a problem. But the really cool part of this is that it reminded me that the OUTPUT clause has a field list.

I’m going to use the same table but I’m going to make the assumption that my Archive table is hundreds of GB in size and getting rid of the identity column just isn’t a realistic option. As the error said, in order to make this work, I’m going to have to set IDENTITY_INSERT ON on Archive and I’m going to have to specify a column list.

CREATE TABLE Source (id int not null identity(1,1), col1 int, col2 int);
INSERT INTO Source VALUES (1,1),(1,1),(1,1),(1,1);
SELECT TOP 0 * INTO Archive FROM Source ;
GO
SET IDENTITY_INSERT Archive ON;
DELETE FROM Source
OUTPUT deleted.id, deleted.col1, deleted.col2
INTO Archive (id, col1, col2);
SET IDENTITY_INSERT Archive OFF;
GO

And there you go. OUTPUT has a column list. Now the reason you don’t see it often is because it’s not really needed all that often. Probably 90% of the time or more you’re going to be doing something very similar to that first example. It’s still helpful to know, though, because, let’s face it, 90% of our time is spent working on that unusual 10%.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL