T- SQL MERGE Statement Tips . 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 isn't 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. You can download the source code to create the database and populate it with sample data.

The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of. Get basics of the SELECT statement in SQL and its keyword clauses in this book excerpt on withdrawing and manipulating data in a SQL database.

Runs a SQL script from a file. The script is a text file containing SQL statements; each statement must end with ';'. This command can be used to restore a database.

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. Dtk Video Capture Control 1 0 6 Exeter more. USE TSQL2. 01. 2; - - download from http: //tsql. 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 doesn't 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.

Creating a table correctly in SQL Server can be pretty tricky, but using SQL Server Management Studio Table Designer makes it easier.

If the input customer ID doesn't 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    DECLARE@custid       INT          = CHECKSUM(SYSDATETIME()),@companyname  NVARCHAR(4. N'A',@country      NVARCHAR(1.

N'B',@phone        NVARCHAR(2. N'C';     MERGE INTO Sales.

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 CHECKSUM(SYSDATETIME()) to generate the customer ID. There's a high likelihood that the resulting value doesn't 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. The atomicity of the MERGE transaction isn't violated - - it's still an all- or- nothing behavior. According to the read committed isolation level, such conflicts aren't 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 session's 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. However, this clause has a different meaning than the ON clause in a join - - which some people don't 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 don't realize this, and you think of ON as a filter, you can end up writing MERGE statements with bugs. I'll demonstrate this problem with an example, and I'll provide a solution. 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. Customers(custid, companyname, country, phone)SELECT custid, companyname, country, phone. FROM Sales. Customers.

WHERE country IN (N'Sweden', N'Italy'); Next, suppose you're given a task to write a MERGE statement that merges customers from Italy from the Sales. Customers table into the Sales. My. Customers table. You're supposed to update customers that exist, and insert customers that don't 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. N'Italy'WHEN 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 you'll get the error message in Figure 2.

This means that if the source has a customer that isn't 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 doesn't exist, you end up inserting a row that you're not supposed to into the target. Here's an example of how you can achieve this with a CTE: WITH SRC AS(SELECT custid, companyname, country, phone. FROM Sales. Customers.

WHERE country = N'Italy')MERGE INTO Sales. My. Customers AS TGTUSING SRCON 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 MERGE USING Clause Is Like the FROM Clause There's an interesting aspect of the MERGE statement that some people aren't aware of. The USING clause of the MERGE statement is designed very similar to the FROM clause in the SELECT statement. This means that you aren't limited to specifying only one table in the USING clause as the source. Rather, you can refer to table expressions, table functions, and even use table operators such as JOIN, APPLY, PIVOT, and UNPIVOT. Eventually, the outcome of the USING clause is a virtual table that's considered the source for the merge operation. Run the following code to create three tables, each holding a different attribute of the customer: IF OBJECT.

Never mind why the customer attributes were originally separated into three tables - - this isn't your concern. You're tasked with developing a MERGE statement that uses the data from the combined tables as the source and the Sales. My. Customers table as the target. You need to implement simple upsert logic - - update where exists and insert where not exists. However, because the USING clause is designed very similar to the FROM clause, you can actually perform the joins directly in this clause. Eventually, the USING clause returns a virtual table representing the result of all table operators, and the result table is used as the source for the merge operation. Here's such a MERGE statement implementing the joins in the USING clause: MERGE INTO Sales.

My. Customers AS TGTUSING Sales. Cust. Company. INNER JOIN Sales. Cust. Country. ON Cust. Company. custid = Cust.

Country. custid - - join ONINNER JOIN Sales. Cust. Phone. ON Cust.

Doc. Comment. Xchange.