OUTPUT and MERGE

Good afternoon everyone! To my English followers, I hope you all had a lovely Bank Holiday weekend!

Over the past couple of weeks, we have been using DML Statements (Data Manipulation Language Statements) in order to make changes to data that is held within a database. These (obviously) consist of INSERT, UPDATE and DELETE statements. We have learnt how to utilise these in multiple scenarios such as in Stored Procedures, Triggers and User-Defined Functions. Now it's time to learn how we can further enhance our statements in order to even further customise a result set that is returned back as a result of executing a query. We can do this using the OUTPUT clause and MERGE statement, which is what today's blog post is going to be about.

What is an OUTPUT Clause?

An OUTPUT clause provides access to the inserted and deleted tables that I mentioned within the last blog post (Please visit Database Triggers if you need a refresher!). This is incredibly useful for tracking changes to data within a database and storing them in a separate location for future reference. OUTPUT can be used with any INSERT, UPDATE or DELETE statement as displayed within the examples below:

INSERT Statement Example:

The INSERT statement example below shows an INSERT into the Customer table. Then, the details of that INSERT are put into a separate "Audit" table. This is useful for tracking data that is being input into the system.

DELETE Statement Example: 

The DELETE statement example below shows an item being removed from the Item table where a DateCreated exists and is before or equal to the 1st of January 2010, and that doesn't belong to a ParentItem (as without a cascade delete, this would cause problems). If any records are removed by this action, they are moved into a DiscontinuedItem table. This is extremely useful when looking at orders that users have purchased and if the item has stopped being stocked however will still have to exist within the system in order for old archived data to be valid.

UPDATE Statement Example: 

The UPDATE statement example below shows a modification to an ItemName, its position determined by the ItemID. In a new table called "ItemChanges", the OldItemName (deleted.ItemName) and the NewItemName (inserted.ItemName).

What is a MERGE Statement?

A MERGE statement allows you to compare rows in two tables and creates a scenario similar to a case statement . Similarly to the OUTPUT clause, MERGE can be used with INSERT, UPDATE and DELETE statements, however unlike using the OUTPUT clause, you declare the MERGE first, and then list any DML statements that may occur due to the state of the comparison of the two tables.

The following subheadings are a simple break down to explain what each part of a MERGE statement does:

INTO <targetTableName>

Defines where rows will be affected as a result of the INSERT, UPDATE or DELETE command.

AS <tableAlias>As mentioned in previous blog posts, an Alias prevents the user from having to retype the same table name over and over again, thus making code more readable.

USING <table_sourceName> 

Defines on which fields the rows should be matched within the source table.

ON <condition>

Defines the conditions that should be used to work out whether the rows in two tables match.

WHEN MATCHED THEN <merge_matched>

Defines the action that should be performed on the row in the target table if there is a match.

WHEN NOT MATCHED THEN <merge_not_matched>

Defines the action that should be performed on the row in the target table if there is no match.

Examples of MERGE Statements

Upon researching MERGE statements, I found this fantastic blog post that describes exactly what a MERGE statement does and gives a very good example, so kudos to the author for the link below: 

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

As a Final Word...

Although I haven't come across OUTPUT and MERGE statements in my working environment, they are incredibly useful to learn. OUTPUT statements are useful for tracking changes through DML statements, while MERGE statements can be used as an alternative to CASE statements in certain circumstances, for example, when you want either an INSERT, UPDATE or DELETE statement to occur depending on the CASE. I hope you have found this blog post useful - if you have any questions please don't hesitate to ask me in the comments box below and I will try to reply as quickly as I can! 

Thanks for reading!