SQL - Inserting and Updating Multiple Records at Once

sql-server

Solution

create a split function, and use it like:

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

I prefer the number table approach

For this method to work, you need to do this one time table setup:

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)

Once the Numbers table is set up, create this function:

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 

You can now easily split a CSV string into a table and join on it:

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)  

To make what you need work, do the following:

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

EDIT thanks to @Emtucifor click here for the tip about the race condition, I have included the locking hints in my answer, to prevent race condition problems when there is high concurrency.

Problem

Original source