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:

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

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