Using OUTPUT with a field list.

2

November 30, 2016 by Kenneth Fisher

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%.

2 thoughts on “Using OUTPUT with a field list.

  1. kast218 says:

    Hi, Kenneth, very nice article as always. Some useful tip for INSERT INTO statement: if you want that new table didnt have identity column just add some arithmetic operation, for example, + 0 or * 1. In your case it will:
    SELECT TOP 0 id + 0 AS id INTO Archive FROM Source;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: