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

Question

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

此存储过程采用逗号分隔的许可 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

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

Answer 1

创建一个 split 函数，并按如下方式使用它:

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,,,')

输出:

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

输出:

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关于竞争条件的提示，我在答案中包含了锁定提示，以防止高并发时出现竞争条件问题。