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:
- Insert with Output.
- Delete with Output.
- Update with Output.
- 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 TABLE DBO.EMP
- (
- ID INT IDENTITY(1,1) PRIMARY KEY,
- NAME VARCHAR(100) ,
- SALARY MONEY,
- DEPT INT
- )
-
-
-
- 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 TABLE DBO.EMP
-
-
- 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 TABLE DBO.EMP
-
-
- 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 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:
- Inserted: Means the rows of column values that are after the update.
- Deleted: Means the rows of column values that are before the update.
-
- 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 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.
- The table doesn't contain a foreign key column.
- The table doesn't participate in triggers.