



SQL Server 2008 T-SQL MERGE Statement Example Microsoft SQL Server 2008 has T-SQL enhancements like SQL MERGE statement for sql developers and SQL Server database administrators (DBAs). SQL Server 2008 MERGE T-SQL command can be used to insert, update and/or delete in one sql statement.

T-SQL Merge command will compare two sets of data or two tables.

If there are matched ones than Merge command will update matched ones.

If there are unmatched rows from one set of data into other then Merge will insert missing rows.

Merge command can be also used to delete unmatched ones from the primary table.



Here is a Merge example that you should run on MS SQL Server 2008 databases to view the results of T-SQL MERGE command.

CREATE TABLE Books (

BookId smallint,

Title varchar(100),

Author varchar(25),

ISBN varchar(50),

Pages int

)

GO

INSERT INTO BOOKS VALUES

(1, 'Microsoft SQL Server 2005 For Dummies', 'Andrew Watt', NULL, NULL),

(2, 'Microsoft SQL Server 2005 For Dummies', NULL, NULL, 432),

(3, 'Microsoft SQL Server 2005 For Dummies', NULL, '978-0-7645-7755-0', NULL)

GO

SELECT * FROM Books

Here is how the data in sql table Books is shown after the SELECT statement execution.

As a T-SQL Developer, I want to convert the above 3 rows of data into a single row.

In a way I want to group these columns taking the book title as a base.

Also I want to merge these three rows into a single row. This means while updating column values of a single row, I have to get rid of the other table rows. This means I will delete duplicate rows as taking the book title as base for compare these all sql table rows.

The below t-sql GROUP BY statement select the data I want, but does not update the row or delete duplicate rows in sql table.

SELECT

MAX(BookId) BookId,

Title,

MAX(Author) Author,

MAX(ISBN) ISBN,

MAX(Pages) Pages

FROM Books

GROUP BY Title

Using MS SQL Server 2008 T-SQL Merge command we will now build one single statement which will update one row and delete the others from the sql table.

Here is the code for t-sql merge example

MERGE Books

USING

(

SELECT

MAX(BookId) BookId,

Title,

MAX(Author) Author,

MAX(ISBN) ISBN,

MAX(Pages) Pages

FROM Books

GROUP BY Title

) MergeData ON Books.BookId = MergeData.BookId

WHEN MATCHED THEN

UPDATE SET

Books.Title = MergeData.Title,

Books.Author = MergeData.Author,

Books.ISBN = MergeData.ISBN,

Books.Pages = MergeData.Pages

WHEN NOT MATCHED BY SOURCE THEN DELETE;

GO

SELECT * FROM Books

And the result set of the above select statement after the Merge command is executed is just as we have targeted.

I hope you liked this SQL Server 2008 T-SQL Merge example.

You can find a similar tutorial on Merge command at SQL Server 2008 T-SQL Merge Command Enhancement and Example titled article.

Also please refer to SQL Merge in Trigger for more SQL Server Merge command examples. Here SQLCMD Utility and SQL Merge command are used together to build a handy tool for database administrators.









Tweet

Related SQL Resources



