SQL Server 2008 T-SQL MERGE Statement Example
Microsoft SQL Server 2008 has T-SQL enhancements like 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.
|