T-SQL supports the OUTPUT clause after the inception of SQL server 2005 and later editions. We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows.
We primarily use the OUTPUT clause for auditing and archiving modified rows. In this tutorial, we will walk through the use of the OUTPUT clause with different DML statements and examples. First, we will create a table, dbo.Songs, and populate it with some data.
IF OBJECT_ID('dbo.Songs') IS NOT NULL
DROP TABLE dbo.Songs
GO
CREATE TABLE dbo.Songs
(
Id int CONSTRAINT PK_Songs_Id PRIMARY KEY,
Name varchar(200) NOT NULL,
Singer varchar(50) NOT NULL
)
GO
INSERT INTO dbo.Songs ( Id, Name, Singer)
VALUES (1, 'I hate everything about you', 'Adam Gontier');
INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES (2, 'Dil se', 'A. R. Rahman');
INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES
(3, 'My heart will go On', 'Celine Dion');
INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES (4, 'Maeri', 'Euphoria');
GO
SELECT * from dbo.Songs
GO
Inserted and Deleted Tables in an OUTPUT Clause
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 Inserted table, and the results of the Delete statement are stored in the Deleted table. Also, with an UPDATE statement, the deleted rows are stored in the Deleted table. The new inserted rows in the Inserted table as UPDATE are nothing but delete and insert operations combined together.
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 OUTPUT Clause with an Insert statement
When we do an Insert operation on a table, we get a message which reads, “(n row(s) affected),” but if we want to see what data rows were inserted into a table, we can use an OUTPUT clause and a memory resident inserted table to return the results back to the screen in the same way that a select statement does.
Let us insert a record, and use an OUTPUT clause to print the results on the screen.
INSERT INTO dbo.Songs ( Id, Name, Singer)
OUTPUT INSERTED.ID, INSERTED.name, INSERTED.Singer
VALUES (5, 'AINT no grave', 'Johnny Cash');
GOCheck the dbo.Songs table. A new row is inserted with id=5.
select * from dbo.Songs;
GO
The OUTPUT Clause with a Delete Statement
The same goes with a delete operation. It shows only (n rows(s) affected). We can use an OUTPUT clause and a deleted table to see which rows were actually deleted from the table.
DELETE from dbo.Songs
OUTPUT DELETED.id, DELETED.name, DELETED.singer
WHERE ID=5;
GOQuery the dbo.Songs table row with id = 5 has been deleted.
select * from dbo.Songs;
GO
The OUTPUT Clause with an Update Statement
An Update statement does nothing but delete old data and insert new data, so with an Update statement, both memory resident tables are affected and are deleted as well as inserted.
Here we are updating the name of a singer, who has sung ‘Dil se’ song, with ID equal to two.
UPDATE dbo.Songs
SET Singer = 'Rahman'
OUTPUT DELETED.Singer, INSERTED.Singer
WHERE ID = 2;
GOYou can see the old singer’s name along with the new singer’s name.
select * from dbo.Songs;
The three examples above show how to use an OUTPUT clause for auditing purposes. Now, we will see how to use it for archiving.
Before, we were just printing the results of a DML statement on the screen, which was temporary, but with the OUTPUT clause, you can store the results of a DML statement in a table, too.
Store Results of an OUTPUT Clause into a 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.Songs_Inserted') IS NOT NULL
DROP TABLE dbo.Songs_Inserted
GO
CREATE TABLE dbo.Songs_Inserted
(
Id int CONSTRAINT PK_Songs__Inserted_Id PRIMARY KEY,
Name varchar(200) NOT NULL,
Singer varchar(50) NOT NULL
)
GO
INSERT INTO dbo.Songs ( Id, Name, Singer)
OUTPUT Inserted.* INTO dbo.Songs_Inserted
VALUES (5, 'Duniya', 'Piyush Mishra');
GO
-- Result of Songs_Inserted table and base table.
select * from dbo.Songs_Inserted;
select * from dbo.Songs;
GO
As the results above show, data is inserted into both the tables.
Store Results of an OUTPUT Clause into a Temporary Table
The same goes with a temporary table. Create a temporary table first, and then using an OUTPUT INTO clause, insert the data returned by the OUTPUT clause into a temporary table.
IF OBJECT_ID('tempdb..#Songs_Deleted') IS NOT NULL
DROP TABLE dbo.#Songs_Deleted
GO
CREATE TABLE dbo.#Songs_Deleted
(
Id int,
Name varchar(200) NOT NULL,
Singer varchar(50) NOT NULL
)
GO
DELETE from dbo.Songs
OUTPUT deleted.* INTO dbo.#Songs_Deleted
WHERE ID IN (4,5);
GO
-- Result of temporary table and base table.
SELECT * from dbo.#Songs_Deleted;
Select * from dbo.Songs;
Store Results of an OUTPUT Clause into a Table Variable
Nothing changes for table variables as well. Declare a table variable structure the same as a source table. Do not forget to run the entire script at once so that you can see the output inserted into a table variable.
Declare @Songs_Deleted TABLE
(
Id int,
Name varchar(200) NOT NULL,
Singer varchar(50) NOT NULL
)
DELETE from dbo.Songs
OUTPUT deleted.* INTO @Songs_Deleted
WHERE ID IN (1,2);
-- Result of table variable
SELECT * from @Songs_Deleted;
Browse through our SQL server archive articles for more useful information.
