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.

Example

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

CREATE TABLE MyCustomers
(
MyID INT IDENTITY(1, 1)
, MyCustomer VARCHAR(200)
, Active TINYINT
);
GO
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
VALUES
(‘Wile E Corp’, 1);

I also need to output my table variable

SELECT
*
FROM
@customers;

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.

SQLNewBlogger

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?

Reference

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

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to The OUPUT Clause in an INSERT–#SQLNewBlogger

  1. Curt says:

    I use OUTPUT a lot. It is very handy in two scenarios: (1) adding multiple rows to both tables of a parent-child relationship where the parent table has an identity column as PK; and (2) caching a record of rows updated or inserted to a table variable or temp table so that it is possible to produce a result set of all affected rows for review, which is often very handy when testing a new multiple-step DML script.

  2. Can the OUTPUT be directed to a TEMP table, or just a table variable? My understanding is that you can do it also to TEMP or regular tables, but I want to confirm.

Comments are closed.