From the BlogSubscribe Now

Preview Inserts, Updates, Deletes, and Merges in SQL Server

With the complex and convoluted database structures out there in the wild in corporate America, often times you run into statements that could scare you half to death. This is especially true when dealing with Inserts, Updates, Deletes and Merges.

Here is a quick and simple way to test the outcome of your changes before actually executing the change.

The important parts are:

BEGIN TRANSACTION – Starts the transaction.

OUTPUT inserted.* – Outputs the result of the change to the results window.

ROLLBACK TRANSACTION – Reverses the effects of the change, leaving the data in the same state it started in.

Example of Transactions in SQL Server

Here is a quick example of a transaction in SQL Server. This is done so that if there is an error, it will undo any changes that it made.
You’ll notice that I’m setting XACT_ABORT to ‘ON’. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

There is also a Try-Catch block, when an error occurs within the try block, it will automatically move to the catch block and execute whatever is in that block. In this case, I’m checking the @@TRANCOUNT, which increments when you begin a transaction (signaling that a change is taking place). If the @@TRANCOUNT is greater than one, it means that the statement has at least tried to execute the statement in the TRY block, so I’m rolling back the transaction if this occurs.

Using OUTPUT Clause to return inserted identity value (or any inserted value) in SQL Server

I recently came across a bug in SQL Server 2008 that can cause the SCOPE_IDENTITY() Function to return an incorrect value for the recently inserted row if the query has a parallel execution plan.  While SCOPE_IDENTITY() is generally a pretty safe way to go, it is not fool-proof.  The surefire way to get the correct value is to use an output clause to return the value needed instead. Here is an example of using the OUTPUT clause to get the (in this case auto-incremented) identity value of the recently inserted row:

This should be the standard going forward and is much better than Selecting SCOPE_IDENTITY() or @@IDENTITY at the end of the query to get your recently inserted row’s identity column.

It should also be noted that you can return more than just the identity column, you can return as many inserted columns as you want to and it will return the values:

This will return the identity and the timestamp that were just inserted:

output clause values

So you can then retrieve and use those values how you normally would if you were using a select statement.