MSSQL2008R2新功能：
MERGE INTO    dbo.T1 AS A                            --目标表
USING        dbo.T1 AS B ON A.custid=B.custid    --来源表，MERGE谓词
WHEN MATCHED THEN                                --当一个来源行与一个目标行匹配
UPDATE SET    A.companyname=B.companyname,        --根据来源行修改目标行
            A.phone=B.phone,
            A.[address]=B.[address]
WHEN NOT MATCHED THEN                            --当一个来源行找不到与之匹配的目标行
INSERT    (                                        --添加到目标表
            custid,
            companyname,
            phone,
            [address],
            inactive
        )
VALUES    (
            B.custid,
            B.companyname,
            B.phone,
            B.[address],
            0
        )
WHEN NOT MATCHED BY SOURCE THEN                    --当一个目标行找不到与之匹配的来源行
DELETE;                                            --删除目标行

------解决方案--------------------

SQL code
    USE tempdb;  
    GO  
    IF OBJECT_ID (N'Target', N'U') IS NOT NULL   
        DROP TABLE dbo.Target;  
    GO  
    Create table Target(ID int ,Name nvarchar(10))  
    insert into Target  
    values(1,'a'),(3,'b'),  
    (5,'c'),(10,'d')  
      
      
    GO  
    IF OBJECT_ID (N'dbo.Source', N'U') IS NOT NULL   
        DROP TABLE dbo.Source;  
    GO  
    Create table Source(ID int ,Name nvarchar(10))  
    insert into Source  
    values(2,'E'),(4,'F'),  
    (6,'H'),(10,'I')  
      
      
    /*Target--源表  
      
    ID  Name  
    1   a  
    3   b  
    5   c  
    10  d  
    */  
      
    /*Source--目标表  
    ID  Name  
    2   E  
    4   F  
    6   H  
    10  I  
    */  
      
    go  
    begin tran  
    merge Target as T  
    using Source as S  
    on (T.ID=S.ID)  
    when matched and T.Name<>S.Name                   --ID相同，Name不同时更新  
        then update set T.Name=S.Name  
    when not matched  then                  --这里可不用写by Target(not matched by Target )没有的ID,新增  
        insert (ID,Name)values(S.ID,S.Name)  
    when not matched by source then         --删除Target表在Source表没有的记录  
        delete  
    OUTPUT $action,   
           inserted.ID AS SourceID, inserted.Name AS SourceName,   
           deleted.ID AS TargetID, deleted.Name AS TargetName;  
             
    select * from Target  
    select * from Source  
      
    rollback tran  
    /*$action  
    $action SourceID    SourceName  TargetID    TargetName  
    INSERT  2   E   NULL    NULL  
    INSERT  4   F   NULL    NULL  
    INSERT  6   H   NULL    NULL  
    DELETE  NULL    NULL    1   a  
    DELETE  NULL    NULL    3   b  
    DELETE  NULL    NULL    5   c  
    UPDATE  10  I   10  d  
      
    Target  
    ID  Name  
    10  I  
    2   E  
    4   F  
    6   H  
      
    Source  
    ID  Name  
    2   E  
    4   F  
    6   H  
    10  I  
      
    */  
    go