Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The OUTPUT clause

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.


 

Comments

Posted by peter on 28 January 2011

I like the output clause for what it does, but in using it a few times now I found out some unexpected limitations.

1. output of inserted.* will not contain properly computed values of computed columns.

2. when there is a trigger on a table, you cannot dure a direct output and instead have to redirect the output to a table for later use.

Especially the later one blew me out of the water as it sincirely resistricts the use of the output clause in client software. If you add functionallity that requires triggers, you could break existing applications!

If you ask, me Microsoft did, despite its usefulnes, a poor job when it implemented this clause. The damage will echo for years to come!

Posted by peter on 28 January 2011

Instead of:

... you cannot dure a direct output ...

I mean:

... you cannot use a direct output ...

Leave a Comment

Please register or log in to leave a comment.