OUTPUT Clause in sql server

OUTPUT Clause in SQL Server

Hello friends, We have already explain regarding IIF() in SQL Server,Right Join in SQL Server,left join in SQL, Self Join in SQL Server , RANK(), DENSE_RANK(), ROW_NUMBER(), Aggregate functions in sql server and Create XML in sql server. Today i will explain OUTPUT Clause in SQL Server.

As name suggest it will definitely return something :-). SCOPE_IDENTITY() will gives you last affected row’s primary key. But what if there is more record to insert and want to return all affected rows.!! OUTPUT Clause in SQL Server will make it easy. Return information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement. The result can also be inserted into a table or table variable.

OUTPUT Clause used in following statements:
INSERT
UPDATE
DELETE
MERGE

Following is syntax:

[OUTPUT column_list]
[OUTPUT column_list INTO @table_variable]

Following is syntax for column_list as you can’t write it in any other way:
[{DELETED/INSERTED}.{*/column_name}]

DELETED
Is a column prefix that specifies the value deleted by the insert or update operation. Columns prefixed with DELETED reflect the value before the UPDATE,DELETE Or MERGE statement is completed.
DELETED cannot be used with the OUTPUT clause in the INSERT statement.
INSERTED
Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE,INSERT or MERGE statement is completed.
INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

For example,

Following query will returns all columns inserted from the #MyTable table.

CREATE TABLE #MyTable (
    MyTableId int NOT NULL IDENTITY (1, 1),
    MyTableColOne varchar(50) NOT NULL,
    MyTableColTwo varchar(50) NOT NULL
)

INSERT #MyTable (MyTableColOne, MyTableColTwo)
OUTPUT Inserted.*
VALUES ('Val1','Val2'),
       ('Val3','Val4')

following query will return all columns deleted from the #MyTable table.

DELETE FROM #MyTable
OUTPUT DELETED.*

following query will return all columns updated in the #MyTable table.

UPDATE #MyTable
SET MyTableColOne='test1'
OUTPUT INSERTED.*

Try on your own about inserting result into variable table. Let me know if any query or questions. And off course don’t forget to share your view 🙂

Thanks 🙂

One comment

Leave a Reply