T SQL MERGE Statement Tips Microsoft SQL Server content from SQL Server Pro When a new T SQL feature is introduced in SQL Server, it usually takes some time before users realize certain aspects of working with the feature. In time, people develop best practices for using the feature and discover interesting, nontrivial things about it. In this article, I discuss some tips concerning the MERGE statement that I discovered after several years of working with the statement. I cover four main tips preventing MERGE conflicts, understanding that the MERGE ON clause isnt a filter, realizing that the MERGE USING clause is like the FROM clause in a SELECT statement, and, finally, referring to source table elements in the OUTPUT clause. For the purposes of this article, I use a sample database called TSQL2. You can download the source code to create the database and populate it with sample data. Sometimes as a DBA we need to generate a TSQL script for dropping and creating indexes in our SQL Server databases. However, SQL Server Management Studio does not. In this article, I would like to show the most commonly used case expressions with update statements in SQL Server. MERGE SQL statement Part 2Synchronize the target table with refreshed data from source table MERGE Products AS TARGET USING UpdatedProducts AS SOURCE ON TARGET. Several tips for using the TSQL MERGE statement Preventing MERGE conflicts, understanding that the MERGE ON clause isnt a filter, and more. MERGE Statement With TableValued Parameters. In the article SQL Server 2008 MERGE Statement, it discusses one of the new features of SQL Server 2008, the MERGE. THIS TOPIC APPLIES TO SQL Server starting with 2008 Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Evaluates a list of conditions and. You dont quite have SQL Servers proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. I am wondering if this is possible at all. I want to update column x if a condition is true, otherwise column y would be updated UPDATE table SET CASE CONDITION. Some of my examples use the Sales. Customers table as the source table for the MERGE statement. All of my examples use a table called Sales. My. Customers as the target table. Use the code in Listing 1 to create the table in the TSQL2. USE TSQL2. 01. 2 download from http tsql. TSQL2. 01. 2. IF OBJECTIDSales. My. Customers IS NOT NULL DROP TABLE Sales. My. Customers CREATE TABLE Sales. My. Customerscustid       INT          NOT NULL,companyname  NVARCHAR4. NOT NULL,country      NVARCHAR1. NOT NULL,phone        NVARCHAR2. Using Case In Sql Server Update Statement With Join In Oracle' title='Using Case In Sql Server Update Statement With Join In Oracle' />Using Case In Sql Server Update Statement With Join OracleUsing Case In Sql Server Update Statement With JoinsNOT NULL,CONSTRAINT PKMy. Adobe Acrobat 8 Text Box Font Color In Html. Customers PRIMARY KEYcustid Preventing MERGE Conflicts One of the classic tasks thats handled with the MERGE statement is update where exists and insert where not exists. Some people refer to this task as upsert. For example, suppose you have a stored procedure that accepts as input parameters the attributes of a customer custid, companyname, country, phone. The procedure is supposed to check whether the customer ID already exists in the target table Sales. My. Customers if it does exist, update the target row, overwriting the nonkey columns with the new values, and if it doesnt exist, add a new row. This task can be accomplished with the following MERGE statement MERGE INTO Sales. My. Customers AS TGTUSING VALUES custid, companyname, country, phone AS SRC custid, companyname, country, phone ON SRC. TGT. custid. WHEN MATCHED THEN UPDATESET TGT. SRC. companyname,TGT. SRC. country,TGT. SRC. phone. WHEN NOT MATCHED THEN INSERTVALUES SRC. SRC. companyname, SRC. SRC. phone The problem is that under the default isolation level, read committed, conflicts can occur if two processes execute the procedure at the same time with the same customer ID as input. If the input customer ID doesnt exist in the target table, the MERGE predicate in both cases will return a false, activating in both cases the WHEN NOT MATCHED action INSERT. In such a case, one of the inserts will result in a primary key violation. To prove this, run the code in Listing 2 from two sessions. SET NOCOUNT ON USE TSQL2. BEGIN TRY  WHILE 1 1. BEGIN    DECLAREcustid       INT           CHECKSUMSYSDATETIME,companyname  NVARCHAR4. NA,country      NVARCHAR1. NB,phone        NVARCHAR2. NC     MERGE INTO Sales. My. Customers AS TGTUSING VALUES custid, companyname, country, phone AS SRC custid, companyname, country, phone ON SRC. TGT. custid. WHEN MATCHED THEN UPDATESET TGT. SRC. companyname,TGT. SRC. country,TGT. SRC. phone. WHEN NOT MATCHED THEN INSERTVALUES SRC. SRC. companyname, SRC. SRC. phone END END TRYBEGIN CATCH  THROW END CATCH SET NOCOUNT OFF The code uses an infinite loop that invokes the aforementioned MERGE statement. It uses the expression CHECKSUMSYSDATETIME to generate the customer ID. Theres a high likelihood that the resulting value doesnt already exist as a customer ID in the target table and that after some iterations both sessions will produce the same value. When I ran this code from two sessions, I got a conflict after a few seconds, and one of the sessions generated the error message in Figure 1. Note that this isnt considered a bug. The atomicity of the MERGE transaction isnt violated its still an all or nothing behavior. According to the read committed isolation level, such conflicts arent supposed to be prevented. If you want to serialize access to the object throughout the transaction both the check whether the customer exists and the insertion, you need to use the serializable isolation level. You can achieve this by either setting the sessions isolation level to serializable SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, or by specifying the table hint HOLDLOCK or SERIALIZABLE both have the same meaning against the target table. To test using the serializable isolation level alter the MERGE statement in Listing 2, replace the first line in the statement with the following MERGE INTO Sales. My. Customers WITH HOLDLOCK AS TGT Run the code from two sessions, and this time conflicts shouldnt occur. The MERGE ON Clause Isnt a Filter The MERGE statement uses an ON clause to define the MERGE predicate. However, this clause has a different meaning than the ON clause in a join which some people dont realize. In a join, the ON clause is used for both matching and filtering purposes. In a MERGE statement, the ON clause is used to identify matches and nonmatches and to accordingly determine which WHEN clause to activate. If you dont realize this, and you think of ON as a filter, you can end up writing MERGE statements with bugs. Ill demonstrate this problem with an example, and Ill provide a solution. In my example, Ill use the Sales. Customers table as the source for the MERGE statement and the Sales. My. Customers table as the target. First, run the following code to populate the Sales. My. Customers table with some rows representing customers from Sweden and Italy TRUNCATE TABLE Sales. My. Customers INSERT INTO Sales. My. Customerscustid, companyname, country, phoneSELECT custid, companyname, country, phone. FROM Sales. Customers. WHERE country IN NSweden, NItaly Next, suppose youre given a task to write a MERGE statement that merges customers from Italy from the Sales. Customers table into the Sales. My. Customers table. Youre supposed to update customers that exist, and insert customers that dont exist. But you need to work with only source customers from Italy. If you think of the MERGE ON clause as a filter, you might write a statement such as the following MERGE INTO Sales. My. Customers AS TGTUSING Sales. Customers SRCON SRC. TGT. custid. AND SRC. NItalyWHEN MATCHED THEN UPDATESET TGT. SRC. companyname,TGT. SRC. country,TGT. SRC. phone. WHEN NOT MATCHED THEN INSERTVALUES SRC. SRC. companyname, SRC. SRC. phone Try running this code, and youll get the error message in Figure 2. The reason for the error is that the ON clause isnt a filter it only determines which of the WHEN clauses to activate. This means that if the source has a customer that isnt from Italy, the ON predicate produces a false, and the row is directed to the WHEN NOT MATCHED clause. This clause attempts to insert the row into the target table. If the customer already exists in the target, you get a primary key violation error. If it doesnt exist, you end up inserting a row that youre not supposed to into the target. The solution is to do all the filtering that you need ahead of time in a table expression and use the table expression as the source for the MERGE statement. Heres an example of how you can achieve this with a CTE WITH SRC ASSELECT custid, companyname, country, phone. FROM Sales. Customers. WHERE country NItalyMERGE INTO Sales. My. Customers AS TGTUSING SRCON SRC. TGT. custid. WHEN MATCHED THEN UPDATESET TGT. SRC. companyname,TGT.