SQL Server 2008新特性之Merge

背景

SQL Server开始者在编写修改表的存储过程中，经常会实现如下的逻辑：如果某条数据存在，则更新（或者删除再插入）该条数据；如果不存在，则插入该条数据。

SQL 2008之前的做法

按照上面的逻辑，在SQL Server 2008之前，我们的处理方法往往是：

IF EXISTS...     UPDATE...     OR     DELETE + INSERT ELSE     INSERT...

SQL Server 2008之Merge

那么在SQL Server 2008中，我们再也不需要那么繁琐写这么多的语句，我们可以使用一个Merge语句来取而代之。让我们来做下面的测试：

测试

以下是测试Merge语句的T-SQL语句：

USE test GO SET NOCOUNT ON --create table for testing IF OBJECT_ID('dbo.tb_testMerge','U') IS NOT NULL     DROP TABLE dbo.tb_testMerge; GO     CREATE TABLE dbo.tb_testMerge (     UserID INT IDENTITY(1,1) NOT NULL     ,UserName VARCHAR(100) NOT NULL     ,LastLoginTime DATETIME NOT NULL        CONSTRAINT DF_tb_testMerge_LastLoginTime DEFAULT(GETDATE())     ,DeleteFlag CHAR(1) NULL        CONSTRAINT DF_tb_testMerge_DeleteFlag DEFAULT('N')     ,CONSTRAINT PK_tb_testMerge PRIMARY KEY(        UserID     ) );    --create table for testing IF OBJECT_ID('dbo.tb_testMerge_History','U') IS NOT NULL     DROP TABLE dbo.tb_testMerge_History; GO     CREATE TABLE dbo.tb_testMerge_History (     UserID INT IDENTITY(1,1) NOT NULL     ,UserName VARCHAR(100) NOT NULL     ,LastLoginTime DATETIME NOT NULL     ,CONSTRAINT PK_tb_testMerge_History PRIMARY KEY(        UserID     ) );   --init data INSERT INTO dbo.tb_testMerge(UserName,DeleteFlag) SELECT 'AAA','N' UNION ALL SELECT 'BBB','Y' UNION ALL SELECT 'CCC','N' ; INSERT INTO dbo.tb_testMerge_History(UserName,LastLoginTime) SELECT 'AAA',GETDATE()-1 UNION ALL SELECT 'BBB',GETDATE()-1 ;   --checking data befor merge action SELECT * FROM dbo.tb_testMerge WITH(NOLOCK) SELECT * FROM dbo.tb_testMerge_History WITH(NOLOCK)   --let's do merge action MERGE dbo.tb_testMerge_History AS tb_Target   --target table(been operated) USING (        SELECT            UserName            ,LastLoginTime            ,DeleteFlag        FROM dbo.tb_testMerge WITH(NOLOCK)      ) AS tb_Source   --data source ON (tb_Target.UserName = tb_Source.UserName) --we delete the record when matching and deleteflag is 'Y' WHEN MATCHED AND tb_Source.DeleteFlag = 'Y'     THEN DELETE WHEN MATCHED     THEN --we update the record when matching,but deleteflag is not 'Y'     UPDATE        SET tb_Target.LastLoginTime = tb_Source.LastLoginTime WHEN NOT MATCHED BY TARGET --did not match     THEN        INSERT (UserName,LastLoginTime)        VALUES (tb_Source.UserName,tb_Source.LastLoginTime) OUTPUT $action    --output the old data and current data        , INSERTED.UserName AS current_UserName        , INSERTED.LastLoginTime AS current_LastLoginTime        , DELETED.UserName AS previous_Username        , DELETED.LastLoginTime AS previous_LastLoginTime ;   --checking data after merge action SELECT * FROM dbo.tb_testMerge WITH(NOLOCK) SELECT * FROM dbo.tb_testMerge_History WITH(NOLOCK)

 

测试结果集

--结果集一 UserID      UserName          LastLoginTime           DeleteFlag ----------- ----------------- ----------------------- ---------- 1           AAA               2012-02-16 21:20:29.723 N 2           BBB               2012-02-16 21:20:29.723 Y 3           CCC               2012-02-16 21:20:29.723 N --结果集二 UserID      UserName          LastLoginTime ----------- ----------------- ----------------------- 1           AAA               2012-02-15 21:20:29.727 2           BBB               2012-02-15 21:20:29.727 --结果集三 $action    current_UserName  current_LastLoginTime   previous_Username previous_LastLoginTime ---------- ----------------- ----------------------- ----------------- ----------------------- INSERT     CCC               2012-02-16 21:20:29.723 NULL              NULL UPDATE     AAA               2012-02-16 21:20:29.723 AAA               2012-02-15 21:20:29.727 DELETE     NULL              NULL                    BBB               2012-02-15 21:20:29.727 --结果集四 UserID      UserName          LastLoginTime           DeleteFlag ----------- ----------------- ----------------------- ---------- 1           AAA               2012-02-16 21:20:29.723 N 2           BBB               2012-02-16 21:20:29.723 Y 3           CCC               2012-02-16 21:20:29.723 N --结果集五 UserID      UserName          LastLoginTime ----------- ----------------- ----------------------- 1           AAA               2012-02-16 21:20:29.723 3           CCC               2012-02-16 21:20:29.723

 

对5个结果集的解释：

1.       结果集一和二：这个不用解释，就是初始化的数据

2.       结果集三：这个是Merge操作后输出的结果集，其中包括：

a)         DML操作的类型(DELETE/UPDATE/INSERT)

b)        表中DML操作后当前数据(INSERTED.*)，如果不存在则为NULL

c)         表中DML操作前得数据(DELETED.*),如果不存在，则为NULL

3.       结果集四：Source表中的数据，这个是数据来源，没有任何变化

4.       结果集五：target表中的数据，经过Merge操作后，该表的数据有如下三个方面的变化

a)         UserName=’AAA’的这条数据的LastLoginTime被更新

b)        UserName=’BBB’的这条数据被删除

c)         UserName=’CCC’的这条数据是新插入的

补充说明

其实，在Oracle9i中已经引入MERGE语句语法，用来合并UPDATE和INSERT语句。在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24374052/viewspace-716761/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24374052/viewspace-716761/