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;