For my first post I thought I would share about the SQL OUTPUT clause which returns information from modified rows (INSERT, DELETE, UPDATE).

This can be quite useful for returning information to confirmation screens in an application or for auditing and archiving the modified rows.

Inserted and Deleted tables are two memory-resident tables that reside within SQL server and are used with the OUTPUT clause. Whenever any DML (INSERT, DELETE, UPDATE) statement is executed, these tables are populated.

The results of the INSERT statement are stored in the Insert table and the results of the DELETE statement are stored in the Delete table. An UPDATE statement is essentially delete and insert operations combined, therefore the deleted value is written to the Delete table and the new value is written to the Insert table.

On a side note, you cannot directly query Inserted and Deleted tables to see what data they are currently holding, but you can use them with the OUTPUT clause as well as with Triggers.

The below examples will show you how to use the OUTPUT clause for DML statements. This will just print the results on screen.

First let us create a table and insert some values:

IF OBJECT_ID('dbo.Music') IS NOT NULL
DROP TABLE dbo.Music
GO
CREATE TABLE dbo.Music
(
   ID			  INT IDENTITY(1,1) CONSTRAINT PK_Music_ID PRIMARY KEY
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

INSERT INTO dbo.Music (SongName, Artist) 
VALUES	 ('Symphony No.9', 'Wolfgang Amadeus Mozart')
        ,('Don Giovanni','Wolfgang Amadeus Mozart')
        ,('Gabriel''s Oboe','Yo-yo Ma')
        ,('Inception: Dream is Collapsing','Hans Zimmer')
        ,('Cello Suits','Johann Sebastian Bach')
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the INSERT statement

INSERT INTO dbo.Music (SongName, Artist)
OUTPUT INSERTED.ID, INSERTED.SongName, INSERTED.Artist
VALUES ('Swan Lake', 'Pyotr Ilyich Tchaikovsky');
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the DELETE statement

DELETE FROM dbo.Music
OUTPUT DELETED.ID, DELETED.SongName, DELETED.Artist
WHERE ID=5;
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the UPDATE statement

UPDATE dbo.Music
SET Artist = 'Ludwig van Beethoven'
OUTPUT DELETED.Artist, INSERTED.Artist
WHERE ID = 1;
GO

SELECT * FROM dbo.Music;
GO

The next few examples will show you how to store the results in tables, temp tables and table variables.

Example 1 – table:
Inserting the data return from an OUTPUT clause into a table can be done using an OUTPUT INTO clause. Keep in mind that you first need to create the target table which must have the same number of columns and data types that match the source table.

IF OBJECT_ID('dbo.Music_Inserted') IS NOT NULL
DROP TABLE dbo.Music_Inserted
GO
CREATE TABLE dbo.Music_Inserted
(
   ID			  INT IDENTITY(1,1) CONSTRAINT PK_Music__Inserted_ID PRIMARY KEY
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

INSERT INTO dbo.Music ( ID, SongName, Artist)
OUTPUT INSERTED.* INTO dbo.Music_Inserted 
VALUES (5, 'Duniya', 'Piyush Mishra');
GO

-- Result of Music_Inserted table and base table.
SELECT * FROM dbo.Music_Inserted;
SELECT * FROM dbo.Music;
GO

Example 2 – temp table:
With a temp table you will need to do the same as above and create the temp table first and then use the OUTPUT INTO clause to insert the data into the temp table.

IF OBJECT_ID('tempdb..#Music_Deleted') IS NOT NULL
DROP TABLE dbo.#Music_Deleted
GO
CREATE TABLE dbo.#Music_Deleted
(
   ID			  INT 
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

DELETE FROM dbo.Music
OUTPUT DELETED.* INTO dbo.#Music_Deleted
WHERE ID IN (4,5);
GO

-- Result of temporary table and base table.
SELECT * FROM dbo.#Music_Deleted;
SELECT * FROM dbo.Music;

Example 3 – table variable:
You will declare a table variable with the same structure as the source table.

DECLARE @Music_Deleted TABLE
(
   ID			  INT 
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)

DELETE FROM dbo.Music
OUTPUT DELETED.* 
INTO @Music_Deleted
WHERE ID IN (1,2);

-- Result of table variable
SELECT * FROM @Music_Deleted;

Leave a Reply