OUTPUT clause in SQL Server enables us to return the result set of data modification operations such as (INSERT, UPDATE, and DELETE). Depending on the data modification operation the column name need a prefix of (inserted. or deleted.)
Let us see what an output clause is and where it could be advantageous for us in action:
OUTPUT with INSERT
With insert statement we could have the output operation to return the inserted value using inserted.[column name]. This way we could see what values inserted to the table.
insert into dbo.tContact (FirstName, LastName,City) output inserted.Firstname, inserted.LastName,inserted.City values ('Biz','Nigatu','Nazareth' ), ('Jason','Nigatu','Denver'); |
We could also use the OUTPUT clause together with INTO clause to log the output to a table and we could have more than one OUTPUT clause at the same time.
--output with insert and into clauses insert into dbo.tContact (FirstName, LastName,City) --Output to log table output inserted.Firstname, inserted.LastName,inserted.City into dbo.tLogs (FirstName, LastName,City) --Output for users to see output inserted.Firstname, inserted.LastName,inserted.City values ('Biz','Nigatu','Nazareth' ), ('Jason','Nigatu','Denver'); |
OUTPUT with Delete
Output clause can be used delete statement as deleted.[columnName]
--output with delete delete from dbo.tContact output deleted.FirstName where FirstName = 'Biz'; |
In the same way as above INTO clause can be used to log the deleted records in a different table
--output with delete with into delete from dbo.tContact output deleted.Firstname, deleted.LastName,deleted.City into dbo.tLogs (FirstName, LastName,City) output deleted.Firstname, deleted.LastName,deleted.City where FirstName = 'Jason'; |
OUTPUT with Update
Update statements is described by a combination of insert and delete operations. Where insert operation represent the new value and delete represent the existing value that will be replaced by the new one.
--output with update update dbo.tContact set FirstName = 'Jase' output deleted.FirstName as OriginalValue, inserted.FirstName as UpdatedValue where FirstName = 'Jason'; |
Sometimes we may have to know what action affected the row especially working in data waarehouse ETL and we could use $action function to get which action affected the row.
MERGE Customers USING tSource ON tSource.ID = Customers.ID WHEN MATCHED THEN UPDATE SET FirstName = tSource.FirstName, LastName = tSource.LastName WHEN NOT MATCHED THEN INSERT (ID,FirstName, LastName) VALUES (tSource.ID, tSource.FirstName, tSource.LastName) OUTPUT DELETED.ID, DELETED.FirstName, Deleted.LastName, $action INTO tLog (ID,FirstName,LastName,Operation); --Check what's inside the log table SELECT * FROM tLog; |
Read More
http://technet.microsoft.com/en-us/library/ms177564.aspx