[SQL]如何使用MERGE方法有效地插入或更新数据

这是一个非常普遍的需求，当我们向一张表插入记录的时候，如果该记录已经存在（通常是主键相同/其他条件匹配），我们就不应当重复插入记录，而是更新该记录。

于是我们最原始的想法一般都是有这样一个存储过程/方法/SQL，先判断是否该记录是否存在，然后决定究竟是更新还是插入。

首先要避免的是用程序来回读取，它们起码要被原子性地处理在一起，否则先不说先判断再执行带来增加一次数据库访问给应用带来性能上的开销外，也无法保证在高并发的前提下的正确性。

在参考资料6中，提供了如下一种SELECT XXX, IF XXX IS NULL INSERT, ELSE, UPDATE，当然这也没有什么错，但从SQL Server 2008开始提供MERGE操作专门处理诸如此类的操作，当然也可以含有删除，但这里只提到INSERTorUPDATE。

借用MongoDB的一个方法名Update + Insert = Upsert，写了下面的方法。其实在Microsoft提供的示例也清晰地描述了这个问题，请大家参看参考资料1。

USE [MyDB];
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpsertUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpsertUser]
GO
USE [MyDB];
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#AnalysisForUpsertUser]') AND type in (N'U'))
DROP TABLE [dbo].[#AnalysisForUpsertUser]
GO
CREATE TABLE #AnalysisForUpsertUser
(
    ExistingUserID int,
    ExistingUserName nvarchar(50),
    ExistingUserFullName nvarchar(50),
    ExistingPassword nvarchar(50),
    ExistingState bit,
    ExistingEmail nvarchar(50),
    ExistingUpdateBy nvarchar(50),
    ExistingUpdateTime datetime,
    ExistingRemark nvarchar(50),
    ActionTaken nvarchar(10),
    NewUserID int,
    NewUserName nvarchar(50),
    NewUserFullName nvarchar(50),
    NewPassword nvarchar(50),
    NewState bit,
    NewEmail nvarchar(50),
    NewUpdateBy nvarchar(50),
    NewUpdateTime datetime,
    NewRemark nvarchar(50),
);
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.UpsertUser
    @UserID int,
    @UserName nvarchar(50),
    @UserFullName nvarchar(50),
    @Password nvarchar(50),
    @State bit,
    @Email nvarchar(50),
    @UpdateBy nvarchar(50),
    --@UpdateTime datetime,
    @Remark nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    MERGE tbUser AS target
    USING (SELECT @UserID,@UserName,@UserFullName,@Password,@State,@Email,@UpdateBy,@Remark) AS source 
      (UserID,UserName,UserFullName,Password,State,Email,UpdateBy,Remark)
    ON (target.UserID = source.UserID)
    WHEN MATCHED THEN
        UPDATE SET 
            UserName = source.UserName
            ,UserFullName = source.UserFullName
            ,Password = source.Password
            ,State = source.State
            ,Email = source.Email
            ,UpdateBy = source.UpdateBy
            ,UpdateTime = GETDATE()
            ,Remark = source.Remark
        WHEN NOT MATCHED THEN
            INSERT (UserID,UserName,UserFullName,Password,State,Email,UpdateBy,UpdateTime,Remark) 
            VALUES (source.UserID,source.UserName,source.UserFullName,source.Password,source.State,source.Email,source.UpdateBy, GETDATE(),source.Remark)
        OUTPUT deleted.*, $action, inserted.* INTO #AnalysisForUpsertUser;
END;
GO

EXEC UpsertUser @UserID = 6887, @UserName = N'test6887', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
    @State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目，并可以修改.';
EXEC UpsertUser @UserID = 6889, @UserName = N'test6889', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
    @State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目，并可以修改.';
EXEC UpsertUser @UserID = 6882, @UserName = N'test6882', @UserFullName = N'编辑所有测试', @Password = N'YCRrXZzNrNU=',
    @State = 1, @Email = N'test3@microsoft.com', @UpdateBy = N'PROGRAM', @Remark = N'可以查看所有项目，并可以修改.';
    
SELECT * FROM [MyDB].[dbo].[tbUser]
SELECT * FROM #AnalysisForUpsertUser
GO;

DROP TABLE #AnalysisForUpsertUser;
GO

参考资料：

1、MERGE (Transact-SQL)

2、使用 MERGE 插入、更新和删除数据

3、OUTPUT 子句 (Transact-SQL)

4、判断SQL数据库中函数、存储过程等是否存在的方法

5、T-SQL Insert or update

6、源码：《WebForm :Nearforums论坛 v7.0源码》源文件浏览