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

The OUPUT Clause in an INSERT–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got asked a question about the OUTPUT clause recently and realized I didn’t remember the syntax. I’ve rarely used this, so I had to look it up and thought this would be a good basic post.

The idea with OUTPUT is that the data from the inserted and deleted tables can be output from the INSERT statement, outside of your triggers. This is the same data, but you can access it in the insert.

The format is

INSERT xxx OUTPUT yyyy INTO @zzz VALUES (or SELECT) mmmm

The xxx is your normal insert target, table or view. The yyyy from the OUTPUT clause is a list of items to output. These is a comma separated list of fields in the format of inserted.col1, inserted.col2.

The @zzz is a table variable. No inserts into scalar variables. This has to work with the set based nature of T-SQL. This means you’ll need to declare this variable. The mmmm is your normal insert stuff.


Here’s a quick, short example. Let’s say  I have this table:

, MyCustomer VARCHAR(200)
, Active TINYINT
INSERT dbo.MyCustomers
VALUES (‘Acme’, 1), (‘Roadrunner’, 0), (‘Bugs’, 1)

I want to insert data into the table, and capture the identity value of MyID  as well as the name, separately from the insert. Note, I might really have a TRY..CATCH in production to deal with issues.

If I add a new row, the identity should be 4. I want to capture this. I’ll first declare my OUTPUT variable.

DECLARE @customers( id int, customer varchar(200);

I don’t have to make this match the entire table, I can use a subset.

Next, let’s build the INSERT. I want to capture the two fields from the inserted table, so we’ll include those.

INSERT dbo.MyCustomers
OUTPUT Inserted.MyID
, Inserted.MyCustomer
INTO @customers
(‘Wile E Corp’, 1);

I also need to output my table variable


If I run this, I’ll see this:

2016-03-11 14_19_28-Settings

Of course, I can do other processing with my table variable, using the output elsewhere in code.


This is a quick look at how you can use the OUPUT clause. This took me about 10 minutes to play with and remember the syntax, and 10 more minutes to write.

I’d encourage you to play with this and write your own blogs. What can you discover about this construct?


OUTPUT – https://msdn.microsoft.com/en-us/library/ms177564.aspx

Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


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

Loading comments...