sql-server – SQL – 一次插入和更新多个记录

我有一个存储过程,负责一次插入或更新多个记录.为了提高性能,我想在我的存储过程中执行此操作.

此存储过程采用逗号分隔的许可ID列表和状态.许可证ID存储在名为@PermitIDs的变量中.状态存储在名为@Status的变量中.我有一个用户定义的函数,将这个以逗号分隔的许可ID列表转换为表.我需要遍历每个ID,并在名为PermitStatus的表中插入或更新.

如果不存在具有许可ID的记录,我想添加记录.如果确实存在,我想用给定的@Status值更新记录.我知道如何为单个ID执行此操作,但我不知道如何为多个ID执行此操作.对于单个ID,我执行以下操作：

-- Determine whether to add or edit the PermitStatus
DECLARE @count int
SET @count = (SELECT Count(ID) FROM PermitStatus WHERE [PermitID]=@PermitID)

-- If no records were found,insert the record,otherwise add
IF @count = 0
BEGIN
  INSERT INTO
    PermitStatus
  (
    [PermitID],[UpdatedOn],[Status]
  )
  VALUES
  (
    @PermitID,GETUTCDATE(),1
  )
  END
  ELSE
    UPDATE
      PermitStatus
    SET
      [UpdatedOn]=GETUTCDATE(),[Status]=@Status
    WHERE
      [PermitID]=@PermitID

如何循环我的用户定义函数返回的表中的记录,以根据需要动态插入或更新记录？

解决方法

SELECT
    *
    FROM YourTable  y
    INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach

要使此方法起作用,您需要执行以下一次性表设置：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置Numbers表后,创建此功能：

CREATE FUNCTION [dbo].[FN_ListToTableAll]
(
     @SplitOn  char(1)      --REQUIRED,the character to split the @List string on,@List     varchar(8000)--REQUIRED,the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this WILL return empty rows
    ----------------
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber,LTRIM(RTRIM(SUBSTRING(ListValue,number+1,CHARINDEX(@SplitOn,ListValue,number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue,number,1) = @SplitOn

);
GO

您现在可以轻松地将CSV字符串拆分为表格并加入其中：

select * from dbo.FN_ListToTableAll(',','1,2,3,4,5,6777,')

OUTPUT：

RowNumber   ListValue
----------- ----------
1           1
2           2
3           3
4           
5           
6           4
7           5
8           6777
9           
10          
11          

(11 row(s) affected)

要使您需要的工作,请执行以下操作：

--this would be the existing table
DECLARE @OldData  table (RowID  int,RowStatus char(1))

INSERT INTO @OldData VALUES (10,'z')
INSERT INTO @OldData VALUES (20,'z')
INSERT INTO @OldData VALUES (30,'z')
INSERT INTO @OldData VALUES (70,'z')
INSERT INTO @OldData VALUES (80,'z')
INSERT INTO @OldData VALUES (90,'z')


--these would be the stored procedure input parameters
DECLARE @IDList      varchar(500),@StatusList  varchar(500)
SELECT @IDList='10,20,30,40,50,60',@StatusList='A,B,C,D,E,F'

--stored procedure local variable
DECLARE @InputList  table (RowID  int,RowStatus char(1))

--convert input prameters into a table
INSERT INTO @InputList
        (RowID,RowStatus)
    SELECT
        i.ListValue,s.ListValue
        FROM dbo.FN_ListToTableAll(',@IDList)            i
            INNER JOIN dbo.FN_ListToTableAll(',@StatusList)  s ON i.RowNumber=s.RowNumber

--update all old existing rows
UPDATE o
    SET RowStatus=i.RowStatus
    FROM @OldData               o WITH (UPDLOCK,HOLDLOCK) --to avoid race condition when there is high concurrency as per @emtucifor
        INNER JOIN @InputList   i ON o.RowID=i.RowID

--insert only the new rows
INSERT INTO @OldData
        (RowID,RowStatus)
    SELECT
        i.RowID,i.RowStatus
        FROM @InputList               i
            LEFT OUTER JOIN @OldData  o ON i.RowID=o.RowID
        WHERE o.RowID IS NULL

--display the old table
SELECT * FROM @OldData order BY RowID

OUTPUT：

RowID       RowStatus
----------- ---------
10          A
20          B
30          C
40          D
50          E
60          F
70          z
80          z
90          z

(9 row(s) affected)

编辑感谢@Emtucifor click here关于竞争条件的提示,我在我的答案中包含了锁定提示,以防止在高并发性时出现竞争条件问题.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!