SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using OUTPUT with a field list.

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...