‘MERGE’ statement is a new feature in SQL Server 2008. It can be used to perform insert, update and delete operation on a destination table simultaneously based on the results of a join with a source table. Well, it sounds like a bit confusing, but let's see an example on how it can help us.
Assume we have following two tables.
“合并”的声明是在SQL Server 2008的新功能。它可以用来执行插入，更新和删除目标表与源表联接的结果，同时根据操作。嗯，这听起来有点混乱，但让我们来看看如何，它可以帮助我们的一个例子。
假设我们有以下两个表。
STUDENT_A
STUDENT_B
Both table are identical in structure (Structure does not need to be identical).
这两个表是相同的结构（结构并不需要是相同的）。
STUDENT_A
STUDENT_B
And we have to update the ‘STUDENT_A’ with the details at ‘STUDENT_B’. We need to compare and if student ID’s are matched, ‘A’ table should be updated with the ‘B’ table. And if the ID’s in ‘B’ Table are new then we have to insert those to the ‘A’ table.
So using the ‘MERGE’ statement we can achieve this in one execution.因此，使用“合并”的声明，我们可以实现在一个执行
Syntax:
MERGE [AS T] USING [AS S] ON [WHEN MATCHED THEN] [WHENNOT MATCHED BY TARGET ] [WHENNOT MATCHEDBY SOURCE ]
And to do the above operation use the following code:
MERGE STUDENT_A AS T USING STUDENT_B AS S ON T.ID = S.ID WHEN MATCHED THENUPDATESET T.AGE = S.AGE WHENNOT MATCHED THENINSERT (ID, FNAME, LNAME, AGE) VALUES(S.ID,S.FNAME,S.LNAME,S.AGE);
**Please note that semicolon ‘;’ is mandatory.
So after executing the above code, and if you inspect the Table ‘A’, you can see that it’s updated the way we wanted.
And also you can use additional rules other than your condition. To illustrate that, first we insert a record to both the tables.
And using the following code you can remove the record with matches the condition and have the value 10.
MERGE STUDENT_A AS T USING STUDENT_B AS S ON T.ID = S.ID WHEN MATCHED and S.ID < 5THENUPDATESET T.AGE = S.AGE WHEN MATCHED and S.ID = 10THENDELETEWHENNOT MATCHED BY TARGET THENINSERT (ID, FNAME, LNAME, AGE) VALUES(S.ID,S.FNAME,S.LNAME,S.AGE);
And if you inspect the table A, you can see that it has the same following results:
