Merge in SQL Server 2008 (not just merge)

The merge statement in SQL Server 2008 can do many things, and its function is to perform INSERT, update, or delete operations on the target table based on the source table. The most typical application is the synchronization of two tables.

The following is a simple example of how the merge statement is used, assuming that there are two table product and productnew in the database, our task is to synchronize the product's data to productnew (of course, the synchronization may be done daily through the job, Here we only focus on the use of the merge).

The following SQL creates a sample table:

--Source Table CREATE table Product (ProductID varchar (7) NOT null PRIMARY KEY, ProductName varchar (+) NOT NULL, P Rice Decimal (13,2) DEFAULT 0);

INSERT into Product Values (' 4100037 ', ' flash drive ', 50), (' 4100038 ', ' mouse ', 30);

--Target Table CREATE table productnew (ProductID varchar (7) NOT null PRIMARY KEY, ProductName varchar (+) NOT NULL, Price Decimal (13,2) DEFAULT 0);

Here's a look at the basic syntax of the merge statement:

MERGE target Table

USING source Table

On match condition

When matched then

Statement

When isn't matched then

Statement

The above is the most basic syntax of the merge, when the statement executes according to the result of the match condition, if the match record is found in the target table then executes the statement after matched then, if no match is found, then executes the statement after not matched then. Note the source table can be a table, or it can be a sub-query statement.

In particular, the semicolon at the end of the merge statement cannot be omitted!

Back to our example, it is clear that the merge match condition of product with the Productnew table is the primary key ProductID field, in which case the Productnew table is empty, and at this point it is definitely executed when the statement is not matched and then. Let's consider the case of incrementing the source table first, the merge statement is as follows:

MERGE Productnew as D USING Product as s on s.productid = D.productid if not matched then INSERT (Pr Oductid,productname,price) VALUES (S.productid,s.productname,s.price);

After 2 rows are affected, we have synchronized the data from the product table to the Productnew table.

Now, we update the price of product table 4100037 products to change them to 55:

UPDATE Product SET price=55 WHERE productid= ' 4100037 ';

We also hope that the updated price should be synchronized to the Productnew table when synchronizing every day, obviously at this point in the merge statement should be added when the matched then statement, the statement to update the price of the Productnew table, add matching the updated merge statement:

MERGE Productnew as D USING Product as s on s.productid = D.productid if not matched then INSERT (Productid,prod Uctname,price) VALUES (s.productid,s.productname,s.price) when matched then UPDATE SET d.productname = S.produc Tname, d.price = S.Price;

2 rows after execution are affected, why is it two lines? Because our matching criteria are only associated by ProductID, the matching record is 2 rows. Additionally, the ProductID field is not updated in our UPDATE statement because it is completely unnecessary (if the ProductID field is modified, it goes directly to not matched).

Now do a damage, we will remove 410037 products:

DELETE Product WHERE productid= ' 4100037 ';

Obviously, the merge statement given above does not synchronize this situation, again back to the definition of the merge statement, to the time of the merge matched then statement to expand slightly:

When not matched by TARGET

Indicates that the target table does not match, by Target is the default, so we directly use when the matched then

When not matched by SOURCE

Indicates that the source table does not match, that is, the target table exists, the source table does not exist in the case.

OK, now we have to complete the source table delete, the target table synchronization action, the merge statement is as follows:

MERGE Productnew as D USING Product as s on s.productid = D.productid when not matched by TARGET then INSERT (Prod Uctid,productname,price) VALUES (S.productid,s.productname,s.price) when not matched by SOURCE then DELETE when Matched then UPDATE SET d.productname = s.productname, d.price = S.Price;

Well, the INSERT, UPDATE, DELETE statements in the merge statement have been used above, which is enough to complete most of the synchronization functions. Of course, there are a number of options for the merge statement, which are not detailed here, please refer to MSDN

Merge in SQL Server 2008 (not just merge)