快速更新插入 Sql server 2008 R2 [英] Fast upsert Sql server 2008 R2

问题描述

我正在尝试使用 SP 将记录更新插入一张表中.

I'm trying to upsert records using SP into one table.

CREATE TABLE [dbo].[SHARE_AD_GROUP](
    [SHARE_AD_GROUP_ID] [int] IDENTITY(1,1) NOT NULL,
    [SHARE_ID] [int] NOT NULL,
    [AD_GROUP] [varchar](200) NOT NULL,
    [SHARE_PERMISSIONS] [varchar](65) NULL,

以下哪种方法最好:

1-

ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]


@shareID int,
@ownerId varchar(200),
@sharePermissions varchar(65)

AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
    if @@ROWCOUNT =0 
    begin
    INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS])
                 VALUES
                       (@shareID,@ownerId,@sharePermissions)
    end

end

2-

   BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    declare @id int
    SET NOCOUNT ON;
    set @id = (select top 1 SHARE_AD_GROUP_ID from SHARE_AD_GROUP where SHARE_ID = @shareID and [AD_GROUP] = @ownerId)
        if @id is null
            begin
                INSERT INTO [dbo].SHARE_AD_GROUP
                       (SHARE_ID,[AD_GROUP],[SHARE_PERMISSIONS])
                 VALUES
                       (@shareID,@ownerId,@sharePermissions)
                 set @id = SCOPE_IDENTITY()
            end
        else
            begin
            UPDATE [dbo].SHARE_AD_GROUP
               SET 
                   [SHARE_PERMISSIONS] = @sharePermissions
             WHERE SHARE_ID = @shareID and [AD_GROUP] = @ownerId
            end
End

3- 合并 ==> 不知道怎么写.

3- Merge ==> No idea how to write that.

您认为最快的方式是什么?

What is the fastest way in your opinion?

推荐答案

我很确定你已经明白了这一点.但有人可能会觉得它很有用.

I'm pretty sure you've already figured this out. But someone might find it useful.

ALTER PROCEDURE [dbo].[PROC_INSERT_SHARE_AD_GROUP]
    @shareID int,
    @ownerId varchar(200),
    @sharePermissions varchar(65)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    MERGE [dbo].SHARE_AD_GROUP AS T
    USING
    (
        SELECT @shareID,@ownerId,@sharePermissions
    ) AS S
    (
        SELECT Share_ID,AD_Group,Share_Permissions
    )
    ON (T.SHARE_ID = S.Share_ID and T.AD_GROUP = S.AD_Group)

    WHEN MATCHED THEN
        UPDATE SET SHARE_PERMISSIONS = S.Share_Permissions

    WHEN NOT MATCHED THEN
        INSERT
            (SHARE_ID,AD_GROUP,SHARE_PERMISSIONS)
        VALUES
            (S.Share_ID,S.AD_Group,S.Share_Permissions)
    ;
END

这篇关于快速更新插入 Sql server 2008 R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！