Wonders of the OUTPUT Clause in SQL Server 2005 - OUTPUT clause with multiple INSERT statements
(Page 3 of 5 )
The previous section concentrated only on single INSERT statements. Let us now examine multiple INSERT statements employing the OUTPUT clause.
Let’s go through the script first.
Use Northwind
Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(7,'NorthWest')
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(8,'SouthEast')
Insert Region(RegionID,RegionDescription)
Output inserted.Regionid,inserted.RegionDescription into @ins
values(9,'NorthEast')
Insert Region
Output inserted.Regionid,inserted.RegionDescription into @ins
values(10,'SouthWest')
--Display Newly Inserted Value will be displayed
Select * from @ins
--Displays all values along with newly Inserted values will be displayed
Select * from Region
The above example is similar to the previous one except for the difference in the number of INSERT statements. In the previous example, we inserted a single row of information, but here we are inserting more than one row. We can specify as many INSERT statements as we want. Every INSERT statement adds a new row to the same virtual table “@ins.”
Finally, we execute the SELECT statement on the virtual table “@ins,” which directly retrieves all the values cached and displays them to the screen.
Next: OUTPUT clause with single DELETE statement >>
More MS SQL Server Articles
More By Jagadish Chaterjee
