SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.

SQL Server 2008 T-SQL merge example

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

SQL Server 2008 T-SQL merge example

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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems