SQL Server 2008 Using the MERGE statement

来源:未知 责任编辑:智问网络 发表时间:2013-11-10 20:24 点击:

‘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

img_scr_001

STUDENT_B

img_scr_002

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_ASTUDENT_B细节我们需要比较如果学生证匹配A”“B”表更新如果“B”的ID然后我们要插入“A”

img_scr_003

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

img_scr_005

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 THENDELETE WHEN NOT MATCHED BY TARGET THEN INSERT (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:

img_scr_005

 

关于更多内容 http://www.2cto.com/database/201203/122260.html

    发表评论
    请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
    用户名: 验证码:点击我更换图片
    最新评论 更多>>

    推荐热点

    • sql常见面试题
    • SQL SERVER 2005性能之跟踪
    • SQL编程(一)
    • LINUX上RMAN自动备份脚本
    • sql server面试题
    • 如何将多个SQL查询统计结果一次显示出来
    • 浅谈SQL Server中的事务日志(三)----在简单恢复模式下日志的角色
    • sql server 列转行
    • SQL SERVER2008日常自动化备份
    网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
    Copyright © 2008-2015 计算机技术学习交流网. 版权所有

    豫ICP备11007008号-1