OUTPUT Clause

INSERT:
=======

USE AdventureWorks;
GO
——–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 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

UPDATE:
=======

USE AdventureWorks;
GO
—-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

DELETE:
=======

USE AdventureWorks;
GO
—-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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s