OUTPUT Clause with an insert statement

So often I run into examples where someone is inserting a row and trying to get one of the values from that row that was just inserted. For instance an integer identity id value or just to be able to see the row after it was inserted.

Introducing the OUTPUT clause, and often overlooked or forgotten tool in SQL Server.

The output clause was added to Microsoft SQL Server in version 2005, and is available in all editions including Express, Standard, Enterprise, Developer and Azure, however many people are not familiar with the power the output clause wields.

Take the following query, which I commonly see something like this.

-- original
BEGIN TRAN
	INSERT INTO test1 (col1, col2, col3) 
	VALUES ('one1', 'two2', cast(newid() as varchar(100)));

	SELECT * FROM test1 WHERE id = SCOPE_IDENTITY();
COMMIT TRAN

You could simplify that code with just a single statement, as shown below. Note, the newid() function, I am just using to generate some “random” characters.

-- improved
INSERT INTO test1 (col1, col2, col3) 
OUTPUT inserted.*
values ('one1', 'two2', cast(newid() as varchar(100)));

There are however some things you prevent you from using the output clause. The output_table cannot:

Have enabled triggers defined on it.
Participate on either side of a FOREIGN KEY constraint.
Have CHECK constraints or enabled rules.

Here is the entire code sample.

create database testInsert;
go
use testInsert;
go

create table test1
(
	id INTEGER IDENTITY NOT NULL,
	col1 varchar(100) NULL,
	col2 varchar(200) NULL,
	col3 varchar(300) NULL
);

GO 


-- original
BEGIN TRAN
	INSERT INTO test1 (col1, col2, col3) 
	VALUES ('one1', 'two2', cast(newid() as varchar(100)));

	SELECT * FROM test1 WHERE id = SCOPE_IDENTITY();
COMMIT TRAN


-- improved
INSERT INTO test1 (col1, col2, col3) 
OUTPUT inserted.*
values ('one1', 'two2', cast(newid() as varchar(100)));

OUTPUT Clause with an insert statement

Leave a Reply Cancel reply