One of the features that I have been using quite a lot lately is the OUTPUT clause.
This clause can be used with any of the DELETE / INSERT / UPDATE / MERGE statements.
Basically the OUTPUT clause allow us to filter off the affected rows from any of the above statements into another table, a table variable, a temp table or simply to display them to screen.
INSERTED and DELETED tables are used (as with triggers) to store the affected values.
OUTPUT INTO will move the data into a new table \ variable.
OUTPUT will simply display the affected data to screen.
I put together a small example below to demonstate the powerfull features of this clause.
USE tempdb; GO /* CHECK THAT OUR MAIN TABLE "TBL_COUNTRIES" DOES NOT EXIST */ IF EXISTS( SELECT [name] FROM sys.sysobjects where [name] = 'TBL_COUNTRIES') BEGIN PRINT 'DROPPING TABLE "TBL_COUNTRIES"' DROP TABLE TBL_COUNTRIES END ELSE BEGIN PRINT 'TABLE "TBL_COUNTRIES" DOES NOT EXIST' END /* CHECK THAT THE TABLE THAT WILL HOLD THE DELETED RECORDS "TBL_COUNTRIES_DELETED" DOES NOT EXIST. */ IF EXISTS( SELECT [name] FROM sys.sysobjects where [name] = 'TBL_COUNTRIES_DELETED') BEGIN PRINT 'DROPPING TABLE "TBL_COUNTRIES_DELETED"' DROP TABLE TBL_COUNTRIES_DELETED END ELSE BEGIN PRINT 'TABLE "TBL_COUNTRIES_DELETED" DOES NOT EXIST' END /* CREATE A TABLE TO HOLD THE NAMES OF OUR COUNTRIES */ CREATE TABLE TBL_COUNTRIES ( COUNTRY_ID INT IDENTITY(1,1), COUNTRY_NAME VARCHAR(50) ); /*INSERT SOME VALUES INTO THE COUNTRIES TABLE*/ INSERT INTO TBL_COUNTRIES VALUES ('NEW ZEALAND'), ('UK'), ('AUSTRALIA'), ('USA'), ('FRANCE') GO /* CREATE THE TABLE TO HOLD THE DELETED RECORDS */ CREATE TABLE TBL_COUNTRIES_DELETED ( COUNTRY_ID INT IDENTITY(1,1), COUNTRY_NAME VARCHAR(50), DATE_DELETED DATETIME2 /*STORE THE TIME OF THE DELETE*/ ); /*NOW RUN THE DELETE STATEMENT*/ DELETE TBL_COUNTRIES /*THE FIRST OUTPUT CLAUSE WILL PLACE THE DELETED RECORDS IN A TABLE*/ OUTPUT deleted.COUNTRY_NAME , GETDATE() INTO TBL_COUNTRIES_DELETED /*THE SECOND OUTPUT CLAUSE WILL DISPLAY THE RECORDS*/ OUTPUT deleted.* , GETDATE() [Date_Deleted] GO
Now if we do a select from our table "TBL_COUNTRIES_DELETED", we will see all of our deleted data - although it's already been displayed using the additional OUTPUT clause.
A pretty cool feature that can be used to ensure you did only delete \ update the data that you expected.