Synchronizing tables with the merge statement

Build a test environment first: Use Tempdbgoif object_id (' T1 ') was NOT null drop table t1if object_id (' T2 ') was NOT null drop table T2gocreate tabl E T1 (ID1 int,val1 varchar) CREATE TABLE T2 (ID2 int,val2 varchar ()) Goinsert into T1select 1, ' A ' UNION allselect 2, ' B ' UNION allselect 3, ' C ' now our goal is to synchronize the T2 table with the T1 table, I directly post the complete merge statement, and so on, and then elaborate on the various parts:

MERGE into T2 as tb_targetusing T1 as Tb_sourceon tb_target. Id2=tb_source. Id1when not matched by TARGET then INSERT (id2,val2) VALUES (ID1,VAL1) when not matched by SOURCE Thendeletewhen matched and T B_target. Val2<>tb_source. VAL1 then UPDATE settb_target. Val2=tb_source. Val1output $ACTION, ISNULL (DELETED. Id2,inserted. ID2) as id,deleted. Val2,inserted. VAL2. See the results of the merge statement output/* $ACTION ID2 VAL2 VAL2---------------------           ----------------------------------------------------------------------------------------------------INSERT 1                                               NULL Ainsert 2 NULL         Binsert 3 NULL c*/look at what's now T2: SELECT * from T2/*id2 VAL2-------------------------------------------------------------1 A2 B3 c*/can see the T1 Has passed, which means that the initial synchronization is complete. Now doing some other operations, we insert,New, delete a data: UPDATE T1 SET val1= ' D ' where id1=3delete from T1 where Id1=2insert to T1select 4, ' E ' SELECT * from T1/*ID1 VAL1-------------------------------------------------------------1 A4 E3 d*/All kinds of data are now available, 1 Unchanged, 2 was deleted, 3 was changed, and 4 was added. Then execute the top merge statement: Merge into T2 as tb_targetusing T1 as Tb_sourceon tb_target. Id2=tb_source. Id1when not matched by TARGET then INSERT (id2,val2) VALUES (ID1,VAL1) when not matched by SOURCE Thendeletewhen matched and T B_target. Val2<>tb_source. VAL1 then UPDATE settb_target. Val2=tb_source. Val1output $ACTION, ISNULL (DELETED. Id2,inserted. ID2) as id,deleted. Val2,inserted. val2;/* $ACTION ID VAL2 VAL2-----------------------------------                                               --------------------------------------------------------------------------------------INSERT 4 NULL Edelete 2 B NULLUPDA     TE3 C d*/Look at the T2 data select * FROM T2/*id2 VAL2------------ -------------------------------------------------1 A3 D4 e*/can see that the data is fully synchronized. After seeing the effect, we can start to say the text, I paste the merge statement again, then a sentence in detail merge into T2 as tb_targetusing T1 as Tb_sourceon tb_target. Id2=tb_source. Id1when not matched by TARGET then INSERT (id2,val2) VALUES (ID1,VAL1) when not matched by SOURCE Thendeletewhen matched and T B_target. Val2<>tb_source. VAL1 then UPDATE settb_target. Val2=tb_source. Val1output $ACTION, ISNULL (DELETED. Id2,inserted. ID2) as id,deleted. Val2,inserted. VAL2; 1.MERGE into T2 as tb_target specifies the target table to synchronize. Merge is Keyword,into dispensable, T2 is the target table name, as dispensable, Tb_target is the table alias. Suppose you want to add table hints and index hints to the target table, such as with (...), plus the middle of T2 and as. 2.USING T1 as Tb_source specifies the table or other stuff that is used as the source of synchronization. Using is Keyword,t1 is the name of the original table or a subquery, for example, a bunch of joins come out of things enclosed in parentheses. As ibid., Tb_source is an alias. 3.ON Tb_target. Id2=tb_source. ID1 Association conditions, there is nothing to say, note that the above-defined alias is used here to start. 4.WHEN not matched by TARGET Theninsert (id2,val2) VALUES (ID1,VAL1) put together here to say. See INsert should be able to guess the meaning of this sentence is "If the original table has a record of the new table is not, insert." Not matched represents a mismatch, by Target indicates that the new table cannot find a record that matches the original table condition (that is, the above on write), by Target can not write, by default is by target, but suppose to write two when matched will need to write, For example, the above merge. The 23rd line is almost identical to the normal INSERT statement, except that there is no target table name and only values can not be used with SELECT, because this is a single row operation. 5.WHEN not matched by SOURCE Thendelete This is simple, assuming that the original table can not find a matching record of the new table, the new table is deleted. It is important to note that if you add this sentence, the not matched above must be added by TARGET. 6.WHEN matched and Tb_target. Val2<>tb_source. VAL1 then UPDATE settb_target. Val2=tb_source. VAL1 the and part of the first line can not, equivalent to another update match condition, as in the above example, the data with ID 1 is not moving, but because the matching record can be found or update, plus the condition can avoid such invalid operation. 7.OUTPUT $ACTION, ISNULL (DELETED. Id2,inserted. ID2) as id,deleted. Val2,inserted. VAL2 This line can be removed, the role is to output synchronized data, using the trigger of the classmate on the inserted and deleted two tables should be familiar with gray, respectively put the updated value and the value before the update, to see the last merge output information can almost identical to see the doorway, I'm not going to say much. If you want to debug the statement, you can add this sentence, the normal synchronization can be removed. 8. This must have ... In short, 4,5,6,7 can be removed, but 4,5,6 at least one, this is the merge of all the regular use of grammar. The other last option query hints at the end of a simple comparison of the merge and the original effect of the operation of the IO control merge into T2 as tb_targetusing T1 as Tb_sourceon tb_target. Id2=tb_source. Id1when not matched by TARGET so INSERT (id2,val2) VALUES (id1,val1) when NOT matched by SOURCE Thendeletewhen matched and tb_target. Val2<>tb_source. VAL1 then UPDATE settb_target. Val2=tb_source. Val1output $ACTION, ISNULL (DELETED. Id2,inserted. ID2) as id,deleted. Val2,inserted. VAL2; /* table ' T2 '. Scan count 2, logical read 7 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' T1 '. Scan count 2, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. */PRINT '------------------------------------------------------------------------------------' INSERT into T2 (ID2, VAL2) Select Id1,val1from T1 where not EXISTS (SELECT 1 from T2 where T2. Id2=t1. ID1) UPDATE T2set T2. Val2=t1. Val1from t2inner JOIN T1 on T2. Id2=t1. Id1and T2. Val2<>t1. Val1delete from T2 where is not EXISTS (SELECT 1 from T1 where T1. Id1=t2. ID2)/* table ' T2 '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' worktable '. Scan count 1, logical read 5 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' T1 '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' T2 '. Scan count 1, logic read 2 times, physical read 0 times, pre-read 0 times, LoB logic reads 0 times, the LOB is physically read 0 times, and the lob is read 0 times. Table ' T1 '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' T2 '. Scan count 1, logical read 1 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. Table ' T1 '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.  */

Synchronizing tables with the merge statement