[MSSQL]SQL Server 2008 Using the MERGE statement

最新推荐文章于 2024-05-27 17:01:27 发布

厦门德仔

最新推荐文章于 2024-05-27 17:01:27 发布

阅读量1.6k

点赞数

文章标签： sql server merge insert structure table delete

‘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.

和我们有更新的“STUDENT_A与在”STUDENT_B的细节。我们需要比较，如果学生证的匹配，“A”表应与“B”表的更新。如果在“B”表的ID是新的，然后我们要插入的“A”表。

So using the ‘MERGE’ statement we can achieve this in one execution.因此，使用“合并”的声明，我们可以实现在一个执行

Syntax:

MERGE  <Target> [AS T]
USING    <Source> [AS S]
ON <Condition>
[WHEN MATCHED THEN <Execution>]
[WHEN NOT MATCHED BY TARGET <Execution>]
[WHEN NOT MATCHED BY SOURCE <Execution>]

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 THEN UPDATE SET T.AGE = S.AGE
WHEN NOT MATCHED THEN INSERT (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.

insert into STUDENT_A 
select 10, 'John','Doe',30

insert into STUDENT_B 
select 10, 'John','Doe',30

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 < 5 THEN UPDATE SET T.AGE = S.AGE
WHEN MATCHED and S.ID = 10 THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT (ID, FNAME, LNAME, AGE) 
         VALUES(S.ID,S.FNAME,S.LNAME,S.AGE);