转：SQL Server 2008新特性之Merge

转载

wx60f7f54847950 2021-07-22 17:15:47

文章标签 其他 文章分类 代码人生

原文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  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 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)