Wonders of the OUTPUT Clause in SQL Server 2005 - OUTPUT clause with single INSERT statement
(Page 2 of 5 )
Let us go through the following example, which shall insert a single row into the table and display the result on the screen.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region
Output inserted.regionid,inserted.regiondescription into @ins
values(5,'NorthEast')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted value will be displayed
Select * from Region
The above script is very easy to understand. We are declaring a variable “@ins,” which is of type “table” having two fields, “InsRegionID” (of type “int”) and “InsRegionDescription” (of type “nvarchar”). After that, we are inserting a row into the table "region" by using the “Insert” statement. Let us take a clear look at the “Insert” statement:
Insert Region
Output inserted.regionid,inserted.regiondescription into @ins
values(5,'NorthEast')
The above statement has its own details to understand. The row being inserted into the “region” table must be “cached” using the OUTPUT clause into the virtual table “@ins.” For that reason, we inserted the “OUTPUT” clause, just before the “values” clause. All the values which are going to be inserted will also have a copy to be saved in our virtual table “@ins.” As we are using an INSERT statement here, we need to use the reserved word “inserted.”
Finally, we execute the SELECT statement on the virtual table “@ins,” which directly retrieves all the values cached and displayed onto the screen
How about specifying explicit column names for the INSERT statement? The following example illustrates the same.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region(RegionID,RegionDescription)
Output inserted.regionid,inserted.regiondescription into @ins
values(6,'SouthNorth')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted value will be displayed
Select * from Region
The above example is similar to the previous one except for the difference in the INSERT statement. In the previous example, we didn’t mention any explicit column names for the INSERT statement, but here we mentioned explicit column names. Even though it is not compulsory to work with explicit column names, it is highly recommended that you include them as part of the INSERT statement.
Next: OUTPUT clause with multiple INSERT statements >>
More MS SQL Server Articles
More By Jagadish Chaterjee
