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.