Synchronize tables with MERGE statements

Synchronize tables with MERGE statements

Build the test environment first: use tempdbgoif OBJECT_ID ('t1') is not null drop table T1IF OBJECT_ID ('t2') is not null drop table T2GOCREATE TABLE T1 (ID1 INT, VAL1 VARCHAR (50) create table T2 (ID2 INT, VAL2 VARCHAR (50) goinsert into T1SELECT 1, 'A' union allselect 2, 'B' UNION ALLSELECT 3, 'C' our goal is to synchronize table T2 with table T1. I will post the complete MERGE statement directly, and I will elaborate on each part later:

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 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; check the result output by the MERGE statement/* $ ACTION ID2 VAL2 VAL2 ------------ --------- ---------------------------------------------------------------------------------------------- INSERT 1 null ainsert 2 null binsert 3 null c */check the current T2 content again: SELECT * FROM T2/* ID2 VAL2 ----------- ------------------------------------------------ 1 A2 B3 C */You Can See That T1. that is to say, the initial synchronization is completed. Perform other operations to insert, update, and delete a data record: UPDATE T1 SET VAL1 = 'D' WHERE ID1 = 3 delete from T1 WHERE ID1 = 2 insert into T1SELECT 4, 'E' SELECT * FROM T1/* ID1 VAL1 ---------------------------------------------------------- 1 A4 E3 D */all types of data are available now, 1 is not changed, 2 is deleted, 3 is changed, 4 is added. Run the above 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 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;/* $ action id VAL2 VAL2 ------------ --------- Explain optimize INSERT 4 null edelete 2 B NULLUPDATE 3 C D */check the data SELECT * FROM T2/* ID2 VAL2 ----------- values 1 A3 D4 E */you can see, the data has been fully synchronized. After seeing the effect, we can start to talk about the body. I will stick the MERGE statement again, THEN let's take a closer look at 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 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; 1. MERGE IN TO T2 AS TB_TARGET specifies the target table TO be synchronized. MERGE is a keyword, INTO is dispensable, T2 is the target table name, AS is dispensable, and TB_TARGET is the table alias. If you want to add table prompts and index prompts to the target table, such as with (...), you can add them in the middle of T2 and. 2. USING T1 AS TB_SOURCE specifies the table or other stuff used AS the synchronization source. USING is a keyword. T1 is the original table name or a subquery. For example, a bunch of JOIN items are enclosed in parentheses. AS is the same AS above. TB_SOURCE is an alias. 3. ON TB_TARGET.ID2 = TB_SOURCE.ID1 Association condition. There is nothing to say about it. Note that the alias defined above is used here. 4. when not matched by target theninsert (ID2, VAL2) VALUES (ID1, VAL1) are put together. When we see the INSERT statement, we can guess that this statement means "if there are no new records in the original table, We can INSERT them ". Not matched indicates that the table does NOT match. by target indicates that the new table cannot find a record that matches the original table condition (that is, the record written after the ON Operation). by target can be left blank. The default value is by target, but if you want to write two when matched, you must write it, such as the above MERGE. The second and third rows are similar to normal insert statements. The difference is that there is no target table name and SELECT can only be used for VALUES, because the operations on a single row are used here. 5. when not matched by source thendelete is simple. If the matching record of the new table cannot be found in the original table, delete the new table. Note that if you want to add this sentence, the above not matched 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 after the first line can be equal to another matching condition of the UPDATE, as in the above example, the data with ID 1 is not dynamic, but the matching record will still be updated because it can be found. Adding conditions can avoid this invalid operation. 7. OUTPUT $ ACTION, ISNULL (DELETED. ID2, INSERTED. ID2) as id, DELETED. VAL2, INSERTED. VAL2 can be removed from this line. It is used to output synchronized data. Those who have used triggers should be familiar with the INSERTED and DELETED tables, put the updated value and the pre-update value respectively. Let's look at the information output by MERGE at the last time to see the portal. I will not talk much about it. If you want to debug the statement, you can add this sentence to remove normal synchronization. 8.; this must be ..... In short, can be removed, but at least, 6 must have one, which is all the common syntax of MERGE. Finally, you can add the OPTION query prompt to briefly compare the IO of MERGE and the operations with the same effect. 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 TB_TARGET.VAL2 <> TB_SOURCE.VAL1 then update SETTB_TARGET.VAL2 = =$ ACTION, ISNULL (DELETED. ID2, INSERTED. ID2) as id, DELETED. VAL2, INSERTED. VAL2; /* Table 't2 '. Scan count 2, logical reads 7 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, lob pre-reads 0 times. Table '1 '. Scan count 2, logical read 4, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. */PRINT 'forward' 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 not exists (SELECT 1 FROM T1 WHERE T1.ID1 = T2.ID2)/* Table 't2 '. 1 scan count, 4 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'worktable '. 1 scan count, 5 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table '1 '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table 't2 '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table '1 '. 1 scan count, 4 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 't2 '. Scan count 1, logical read 1, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. Table '1 '. 1 scan count, 4 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. */

What is the full name of MERGE in the merge SQL statement in SQL Server2008? What does it mean? Are there any

Merge is a new sqlserver2008 statement.
SQL Server 2008 will contain New syntaxes used to merge two rowset data. You can use a new MERGE statement to insert, update, and delete a data table from the source table.

When synchronizing information between two tables, you have to perform three steps. First, you must process any new rows that need to be inserted into the target data table. The second step is to process the existing rows to be updated. Delete the old rows that are no longer in use. In this process, a large number of repeated logic needs to be maintained and may cause subtle errors.

Bob Beauchemin discusses the MERGE statement, which combines the preceding multiple operation steps into a single statement. He provides the following example:

Merge [target] t
Using [source] s on t. id = s. id
When matched then update set t. name = s. name, t. age = s. age -- use "rowset1"
When not matched then insert values (id, name, age) -- use "rowset2"
When not matched by source then delete; -- use "rowset3"
As you can see, the specific operation is determined based on the parsing result of the join. In this example, if the target and the source data table have matched rows, the update operation is performed. If not, insert or delete the data table to make it consistent with the source data table.

The beauty of this new syntax is its certainty when processing updates. When Standard UPDATE syntaxes and syntaxes are used, more than one source row may match the target row. In this case, it is unpredictable which source row data will be used for the update operation.

When MERGE syntax is used, if there are multiple matches, it will throw an error. This reminds developers that the current joint conditions are not clear enough to achieve the expected goal.
No ppt
Pdf ishare.iask.sina.com.cn/f/16242519.html

Problems with using merge statements

What is the error?