Output Clause in SQL Server

Introduction

The output clause is very similar to a SELECT Query. We can use the OUTPUT clause in various scenarios.

We can perform the output clause in various statements. That is:

1. Insert with Output.
2. Delete with Output.
3. Update with Output.
4. Merge Statement With Output.

We will see each of them in the preceding in detail.

In a real scenario whenever we are inserting, deleting and updating large or bulk data to the table at the same time we need to return the information Inserted rows, Deleted rows and Updated rows that data back to client application.

With an output clause we can also get the data back with some little bit of effort. By using output it's easy to handle.

Insert with Output

We can use an output clause to return the information from Inserted rows in Insert Statement.

CREATE DATABASE DEMOS
USE DEMOS

--CREATE EMP TABLE
CREATE TABLE DBO.EMP
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    NAME VARCHAR(100) ,
    SALARY MONEY,
    DEPT INT
)


--INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE
INSERT INTO DBO.EMP(NAME,SALARY,DEPT)
OUTPUT INSERTED.*
VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)

 

Figure 1: Insert

When we run the preceding query we get the following output.

 

Figure 2: Insert Output

The following is the output with specified columns only (without *):

--TRUNCATE ABOVE DATA
TRUNCATE TABLE DBO.EMP

--INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE (COLUMN_NAMES)
INSERT INTO DBO.EMP(NAME,SALARY,DEPT)
OUTPUT INSERTED.ID,INSERTED.NAME,INSERTED.DEPT
VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)
DROP TABLE  DBO.EMP

 

Figure 3: Output with Specified Column

When we run the preceding query we get the following output.

 

Figure 4: Specified Column Output

The following is the output with a Column Alias:

--TRUNCATE ABOVE DATA
TRUNCATE TABLE DBO.EMP

--INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE (WITH COLUMN ALIAS)
INSERT INTO DBO.EMP (NAME, SALARY, DEPT)
OUTPUT INSERTED.ID, INSERTED.FIRTSNAME AS [FULL_NAME], INSERTED.DEPT
VALUES ('RAKESH', 24000, 1), ('NARESH', 48000, 1), ('PRAVEEN', 33000, 2)

 

Figure 5: Output with Column Alias

When we run the preceding query we can get the following output.

 

Figure 6: Column Alias Output

Delete with Output

We can use the output clause to return the information from the deleted rows in the Delete statement.

--DELETE FROM EMP TABLE WHOSE DEPT IS -1 (WITH OUTPUT)
DELETE FROM DBO.EMP
OUTPUT DELETED.*
WHERE DEPT=1

 

Figure 7: Delete

Update with Output

We can use the output clause to return the information from updated rows in the update statement.

In update with the output we need to see the following 2 options:

1. Inserted: Means the rows of column values that are after the update.
2. Deleted: Means the rows of column values that are before the update.

--UPDATE FROM EMP TABLE (WITH OUTPUT)
UPDATE DBO.EMP SET SALARY=SALARY+10000
OUTPUT DELETED.SALARY [OLDSALARY], INSERTED.SALARY [NEWSALARY]

 

Figure 8: Update

Merge Statement with Output

We can also use the Output clause with a Merge statement. When we are using the merge statement with the Output clause we need to identify which operation can be performed either insert, update or delete.

For this using $action function.

$action returns the :

If the row is inserted it returns INSERT.

If the row is updated it returns UPDATE.

If the row is deleted it returns DELETE.

--MERGE STATEMENT WITH OUTPUT CLAUSE
MERGE INTO DBO.EMP AS TRG
USING (VALUES ('MADHU',25000,1),('RAKESH',30000,1)) AS SRC (NAME,SALARY,DEPT)
ON SRC.NAME=TRG.NAME
WHEN NOT MATCHED BY TARGET
THEN INSERT  VALUES(SRC.NAME,SRC.SALARY,SRC.DEPT)
WHEN MATCHED AND (TRG.SALARY<>SRC.SALARY OR TRG.DEPT<>SRC.DEPT)
THEN UPDATE SET TRG.SALARY=SRC.SALARY,TRG.DEPT=SRC.DEPT
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $ACTION AS [ACTION],COALESCE(INSERTED.NAME,DELETED.NAME) AS [NAME];

 

Figure 9: Merge Statement

When we run the preceding query we can get the following output.

 

Figure 10: Merge Statement Output

We can also use two output clauses whenever an operation is performed.

One for INTO.

Another for Normal.

 

CREATE TABLE EMP_BACKUP
(NAME VARCHAR(100),DEPT INT)

INSERT INTO DBO.EMP(NAME,SALARY,DEPT)
OUTPUT INSERTED.NAME AS [FULL_NAME],INSERTED.DEPT INTO EMP_BACKUP(NAME,DEPT)
OUTPUT INSERTED.*
VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)

 

Note: When we use INTO with table name the table must follow 2 conditions.

1. The table doesn't contain a foreign key column.
2. The table doesn't participate in triggers.