SQL Server 2008 MERGE 使用实例

2011年3月22日 | 分类: DB | 标签: , , , ,

在SQL Server 2008中增加了MERGE语法,通过使用MERGE可以轻松实现SQL Server数据库中从源表到目标表的数据同步,如根据源表对目标表进行添加、删除和更新行,以下是我实际应用中使用的一个例子,首先创建如下过程,然后在作业中定期调用:

CREATE PROCEDURE [dbo].[SyncAllUsersT]

AS

MERGE AllUsersT AS t
    USING AllUsers AS s
    ON s.distinguishedName = t.distinguishedName
    WHEN NOT MATCHED BY TARGET
        THEN      
 INSERT    (
                  distinguishedName ,
                  whenCreated ,
                  whenChanged ,
                  accountName ,
                  lastName ,
                  firstName ,
                  displayName ,
                  mail ,
                  telephoneNumber ,
                  title ,
                  department ,
                  manager ,
                  ipPhone ,
                  badPwdCount
                )
          VALUES
                ( s.distinguishedName ,
                  s.whenCreated ,
                  s.whenChanged ,
                  s.accountName ,
                  s.lastName ,
                  s.firstName ,
                  s.displayName ,
                  s.mail ,
                  s.telephoneNumber ,
                  s.title ,
                  s.department ,
                  s.manager ,
                  s.ipPhone ,
                  s.badPwdCount
                )
    WHEN NOT MATCHED BY SOURCE
        THEN      
 DELETE
    WHEN MATCHED
        THEN  
UPDATE    SET
        t.whenCreated = s.whenCreated,
        t.whenChanged = s.WhenChanged,
        t.accountName = s.accountName,
        t.lastName = s.lastName,
        t.firstName = s.firstName,
        t.displayName = s.displayName,
        t.mail = s.mail,
        t.telephoneNumber = s.telephoneNumber,
        t.title = s.title,
        t.department = s.department,
        t.manager = s.manager,
        t.ipPhone = s.ipPhone,
        t.badPwdCount = s.badPwdCount;

Written by Nile Jiang
无猖狂以自彰,当阴沉以自深。

目前还没有任何评论.
注意: 评论者允许使用'@user空格'的方式将自己的评论通知另外评论者。例如, ABC是本文的评论者之一,则使用'@ABC '(不包括单引号)将会自动将您的评论发送给ABC。使用'@all ',将会将评论发送给之前所有其它评论者。请务必注意user必须和评论者名相匹配(大小写一致)。