SQL Server 2005 introduced a powerful feature called the OUTPUT clause, which provides access to the inserted and deleted tables, similar to triggers. The OUTPUT clause can be used with INSERT, UPDATE, or DELETE statements to identify the actual rows affected by these statements. It allows you to return values to the client and can generate a table variable, a permanent table, or a temporary table.
Let’s dive into the usage of the OUTPUT clause with some examples:
Example 1: OUTPUT Clause with INSERT Statement
In this example, we will create a table to store permanent data and a temporary table to store the values returned by the OUTPUT clause.
USE AdventureWorks;
-- Creating the table which will store permanent data
CREATE TABLE TestTable (
ID INT,
TextVal VARCHAR(100)
);
-- Creating a temporary table to store the values of the OUTPUT clause
DECLARE @TmpTable TABLE (
ID INT,
TextVal VARCHAR(100)
);
-- Insert values into the real table and use the OUTPUT clause to insert
-- values into the temporary table
INSERT TestTable (ID, TextVal)
OUTPUT Inserted.ID, Inserted.TextVal INTO @TmpTable
VALUES (1, 'FirstVal');
INSERT TestTable (ID, TextVal)
OUTPUT Inserted.ID, Inserted.TextVal INTO @TmpTable
VALUES (2, 'SecondVal');
-- Check the values in the temporary table and the real table
-- The values in both tables will be the same
SELECT * FROM @TmpTable;
SELECT * FROM TestTable;
-- Clean up
DROP TABLE TestTable;
Result:
| ID | TextVal |
|---|---|
| 1 | FirstVal |
| 2 | SecondVal |
Example 2: OUTPUT Clause with INSERT Statement
In this example, we will use the OUTPUT clause with the INSERT statement without storing the values in a temporary table.
USE AdventureWorks;
-- Creating the table which will store permanent data
CREATE TABLE TestTable (
ID INT,
TextVal VARCHAR(100)
);
-- Insert values into the real table and use the OUTPUT clause to display the inserted values
INSERT TestTable (ID, TextVal)
OUTPUT Inserted.ID, Inserted.TextVal
VALUES (1, 'FirstVal');
INSERT TestTable (ID, TextVal)
OUTPUT Inserted.ID, Inserted.TextVal
VALUES (2, 'SecondVal');
-- Clean up
DROP TABLE TestTable;
Result:
| ID | TextVal |
|---|---|
| 1 | FirstVal |
| 2 | SecondVal |
Example 3: OUTPUT Clause with UPDATE Statement
In this example, we will update the values in a table and use the OUTPUT clause to insert the old and new values into a temporary table.
USE AdventureWorks;
-- Creating the table which will store permanent data
CREATE TABLE TestTable (
ID INT,
TextVal VARCHAR(100)
);
-- Creating a temporary table to store the values of the OUTPUT clause
DECLARE @TmpTable TABLE (
ID_New INT,
TextVal_New VARCHAR(100),
ID_Old INT,
TextVal_Old VARCHAR(100)
);
-- Insert values into the real table
INSERT TestTable (ID, TextVal)
VALUES (1, 'FirstVal');
INSERT TestTable (ID, TextVal)
VALUES (2, 'SecondVal');
-- Update the table and insert values into the temporary table using the OUTPUT clause
UPDATE TestTable
SET TextVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TextVal, Deleted.ID, Deleted.TextVal INTO @TmpTable
WHERE ID IN (1, 2);
-- Check the values in the temporary table and the real table
-- The values in both tables will be the same
SELECT * FROM @TmpTable;
SELECT * FROM TestTable;
-- Clean up
DROP TABLE TestTable;
Result:
| ID_New | TextVal_New | ID_Old | TextVal_Old |
|---|---|---|---|
| 1 | NewValue | 1 | FirstVal |
| 2 | NewValue | 2 | SecondVal |
Example 4: OUTPUT Clause with DELETE Statement
In this example, we will delete rows from a table and use the OUTPUT clause to insert the deleted values into a temporary table.
USE AdventureWorks;
-- Creating the table which will store permanent data
CREATE TABLE TestTable (
ID INT,
TextVal VARCHAR(100)
);
-- Creating a temporary table to store the values of the OUTPUT clause
DECLARE @TmpTable TABLE (
ID INT,
TextVal VARCHAR(100)
);
-- Insert values into the real table
INSERT TestTable (ID, TextVal)
VALUES (1, 'FirstVal');
INSERT TestTable (ID, TextVal)
VALUES (2, 'SecondVal');
-- Delete rows from the table and insert the deleted values into the temporary table using the OUTPUT clause
DELETE FROM TestTable
OUTPUT Deleted.ID, Deleted.TextVal INTO @TmpTable
WHERE ID IN (1, 2);
-- Check the values in the temporary table and the real table
-- The values in both tables will be the same
SELECT * FROM @TmpTable;
SELECT * FROM TestTable;
-- Clean up
DROP TABLE TestTable;
Result:
| ID | TextVal |
|---|---|
| 1 | FirstVal |
| 2 | SecondVal |
By exploring these examples, we can see how the OUTPUT clause in SQL Server can be a powerful tool for returning values, tracking changes, and performing various operations on data. It provides a convenient way to work with the inserted and deleted tables, making it easier to handle data manipulation tasks.
Give the OUTPUT clause a try in your SQL Server queries and see how it can simplify your data operations!