The OUTPUT command can be a very useful tool to use at times. The following scripts show a couple of ways it can be used. The code from these scripts can be found here on my shared dropbox.
First off we create a couple of tables for the examples used.
Then populate 10K record using a clever trick using a CROSS JOIN and system tables
Running the following code will delete the number of rows specified in the variable setting so 10 in this case.
It will also display the record that have been deleted using OUTPUT deleted.* as shown below.
You can use the following to reduce the size of delete statements on the transaction log by looping through a table, obviously TRUNCATE is preferred but you cant always use that if Foreign Keys exist or you don’t have permissions
You can also manipulate the deleted statement from OUTPUT. Repopulating the table with the code in the previous examples
The following image shows the results, the Table and column have been concatenated together and col_id, other columns have been ignored
This can start to be come useful when you can output the deleted records into another table. This can be used for Archiving data to Archive tables , Auditing purposes or moving data from one database to another on the same server. Its a single transaction rather then a copy of the data and then a delete.
So if we wanted to move a large table we can merge the technique that was used earlier and loop through the table.
You can use a table variable to stored the output of the data. For example the next set of code stores the record id for each iteration and returns the last record deleted. You can manipulate this how ever you want.
I also used a useful trick highlighted in the red circle below, adding a number after the GO batch separator will run the code from the previous GO statement that many times. So you can see this is ran twice and output the max record for each run.
The OUTPUT statement can also be used with UPDATE statement as well.
As shown below , you can used Deleted.Balance and Inserted.Balance to show the before and after values as records change in the table. Perfect for Auditing changes, and my bank balance is much higher now!
You can get very clever with OUTPUT is can also be used within MERGE statements, which I have blogged about previously here