Sunday, 27 December 2015

OUTPUT Clause with INSERT, UPDATE, DELETE in SQL Server



SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.
OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
OUTPUT clause into Table with INSERT statement
--------Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table to store values of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

----Insert values in real table as well use OUTPUT clause to insert
----values in the temp 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 temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable

----Clean up time
DROP TABLE TestTable
GO

ID          TEXTVal
----------- ----------
1           FirstVal
2           SecondVal

(2 row(s) affected)


ID          TEXTVal
----------- -----------
1           FirstVal
2           SecondVal

(2 row(s) affected)


OUTPUT clause with INSERT statement
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
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 time
DROP TABLE TestTable
GO

ID          TEXTVal
----------- -----------
1           FirstVal

(1 row(s) affected)

ID          TEXTVal
----------- ----------
2           SecondVal
(1 row(s) affected)


OUTPUT clause into Table with UPDATE statement
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))

----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')

INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')

----Update the table and insert values in temp table using 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 temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable

----Clean up time
DROP TABLE TestTable
GO




OUTPUT clause into Table with DELETE statement
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')

INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')

----Update the table and insert values in temp table using Output clause
DELETE
FROM TestTable
OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)

----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable

----Clean up time
DROP TABLE TestTable
GO



No comments:

Post a Comment