ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 2008新特性之Merge

SQL Server 2008新特性之Merge

原创 Linux操作系统 作者:Cherish_j_wang 时间:2012-02-19 21:01:53 0 删除 编辑

背景

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

SQL 2008之前的做法

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

IF EXISTS...

    UPDATE...

    OR

    DELETE + INSERT

ELSE

    INSERT...

SQL Server 2008Merge

那么在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语句语法,用来合并UPDATEINSERT语句。在10gOraclemerge语句做了增强,增加了条件选项和DELETE操作

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-07-08

  • 博文量
    3
  • 访问量
    8458