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! 

Now that I have covered a vast amount of ground with the SELECT statements - I would like to improve and build on the knowledge I know when inserting, updating and deleting records.

Today's blog post will discuss the INSERT, UPDATE and DELETE statements, which will allow you to add, modify and remove data within your database. This will be incredibly useful as one of the main purposes of a database is to keep your data in a secure place where it can be easily modified.

INSERT (or INSERT INTO)

The INSERT statement allows you to add new rows into a database table. A simple INSERT statement is as follows:

Unless otherwise specified, the new row will contain no data and this will need to be considered when adding to columns that are NOT NULL and require some form of value in order to successfully committed to the database:





Note that if you see the INSERT INTO statement (like in the example below), it is only optional and means exactly the same as a normal INSERT statement:

You must always put the values after the INSERT statement within parentheses. You may need to use single quotes to surround the input values if the inputs are string or date format though this will not be necessary for integer values. You also will not need single quotes if you are using a scalar function, for example when you are putting the current date and time stamp in the column.

Identity columns should automatically be generated by SQL Server. Depending on your database schema, you may need to provide some if not all of your column details in order to make your changes and if you do not supply the correct number of required values, you will be presented with the following error when trying to run the query:

INSERT ... SELECT 

The INSERT ... SELECT statement allows you to add rows to an existing table based on rows from a different table. These do not even need to be in the same database, providing you use the database connection string at the beginning to state that is the exact database you are taking the data from, otherwise you will receive an error. An example of this is:

SELECT ... INTO 

You can also use a SELECT ... INTO statement which will allow you to insert data from a table into a brand new table (that can be either temporary or permanent). When creating temporary tables, it is good practice to place a hash symbol (#) at the beginning so this will make it easier to differentiate from your permanent tables. An example of this is shown below:

UPDATE 

Over time, you may wish to update data that already exists within the database. The UPDATE statement allows you to modify the values of multiple columns and rows within a specified table.

When creating an UPDATE statement, you will need to state:

• Where the update is going to take place,
• What is going to be modified,
• What it will be modified to.

An example of this is:

You can also use a FROM clause to UPDATE information based on data from another table such as:

DELETE 

You may wish to delete redundant data from your tables and the DELETE statement will allow you to do this. This is a very simple statement:

However, you have to be very careful when attempting to delete data which has connections to other sub-tables as you will receive an error message to state that this cannot be done. In the example below, I am trying to delete an Item Category by removing it from the ID field:

However, because there are foreign key relationships between another table, the following error message appears and the statement is cancelled and any changes have been rolled back (click on image for full sized version):

You can get around this by using a cascade delete and setting these options in the relationship tab when creating your tables. Under INSERT and UPDATE specific, you can choose to modify both the DELETE Rule and the UPDATE Rule to Cascade so it impacts all related records under that foreign key constraint:

Now when you execute the DELETE statement, all Items related to that Item Category will also be deleted. But, you may not want this to happen in all circumstances, so use cascade wisely.

TRUNCATE

The TRUNCATE statement is typically used to remove all data from a table and performs in a similar way to a DELETE statement with no WHERE clause, however there are some subtle differences that need to be taken into account before you decide which statement is best for the job. Note that this does not impact the structure of the database or its schema at all as the table will still exist until it has been dropped (and we will come onto this in a later blog post). 

An example of a TRUNCATE statement is as follows: 

Where TRUNCATE differs however is that despite the statement removing all data where specified, it will not fire any delete triggers, which makes it much faster than the DELETE statement. You may think that if this is the case, why not just use TRUNCATE all the time? Well firstly, TRUNCATE has no WHERE clause so you can only remove entire data sets from a table rather than specifically stating the exact row and exact column in certain circumstances. Also, you cannot roll back a TRUNCATE statement yet you can with DELETE, which means that if the data is deleted it is permanently gone so be careful! Another point is if you have a foreign key on the table you are trying to TRUNCATE, even if it has no related records from any other table in the database, the statement will not be committed and you will receive an error. You will need to remove these first before you attempt to TRUNCATE.