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

Output into a Table–#SQLNewBlogger

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

I often see people struggling to use triggers for auditing, or having issues with building them to handle multi row updates. However, there’s another choice: the OUTPUT clause.

Not many people use this clause, but it’s a great way to access the virtual inserted and deleted tables in your code.

Suppose I have a simple insert statement like this one:

2016-08-22 11_11_01-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

I want to ensure that I get the data inserted into an audit table. Certainly I could have code that does the insert into two places, like this:

2016-08-22 11_13_40-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

But it can be hard to get developers to use procs like this, they might forget or need to build dynamic SQL. There are the other issues of maintenance where I might update the first INSERT, but forget the second.

OUTPUT allows me to add a clause in my DML statement. I put this before the FROM clause, or in this case, before the Values clause. Then I include values from inserted or deleted along with any scalar values. That looks like this:

2016-08-22 11_16_09-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

However, this returns the data to the console. What I’d really like is to put this into a table. For that reason, I then add an INTO clause, with my table name. Now when I execute this, I get the data from the inserted table added to my EmpAudit table.

2016-08-22 11_18_32-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

A basic, quick look at the OUTPUT clause.

SQLNewBlogger

I ran into an issue with OUTPUT and realized that I hadn’t ever covered this basic concept for myself. As I learned a few things, I decided to write about OUTPUT. This took me about 10 minutes to cover the basics, and was part of a 30 minute session writing a few more pieces on OUTPUT.


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

Comments

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

Loading comments...