SQL - insert and update multiple records at once

Question

I have a stored procedure that is responsible for inserting or updating multiple records at once. I want to do this in my stored procedure for the sake of performance.

This stored procedure accepts a comma-separated list of permission identifiers and status. Permission IDs are stored in a variable called @PermitID. The status is saved in a variable called @Status. I have a custom function that converts this list of comma-delimited permission identifiers to a table. I need to go through each of these identifiers and either insert or update into the PermitStatus table.

If the entry with the permission id does not exist, I want to add the entry. If it exists, I want to update the record with the given @Status value. I know how to do this for a single identifier, but I do not know how to do this for multiple identifiers. For single identifiers, I do the following:

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

How do I iterate over records in a table returned by my user-defined function to dynamically insert or update records as needed?

Answer 1

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 a numerical table approach

For this method to work, you need to complete this setup at once:

 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 configured, 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 

Now you can easily split the CSV row into a table and join 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 do what you need, follow these steps:

 --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 a hint about the state of the race, I included blocking hints in my answer to prevent problems with the state of the races at a high level of concurrency.

Answer 2

There are various ways to complete the tasks you are asking for.

Passing values

There are dozens of ways to do this. Here are some ideas to get you started:

• Pass the line of identifiers and analyze it in the table, then join.
• SQL 2008: join table parameter
• Expect data to exist in and join a predefined temporary table
• Use persistent table with session key
• Put the code in the trigger and join them in INSERTED and DELETED.

Erland Sommarskog offers a wonderful comprehensive discussion of lists in the sql server . In my opinion, the table-value parameter in SQL 2008 is the most elegant solution for this.

Upsert / Merge

• Perform a separate UPDATE and INSERT (two queries, one for each set, not line by line).
• SQL 2008: MERGE.

Important information Gotcha

However, one thing that no one has mentioned is that almost all upsert code, including SQL 2008 MERGE, suffers from race condition problems when there is high concurrency. Unless you use HOLDLOCK and other hints to lock depending on what is being done, you will end up with conflicts. Thus, you need to either block or respond to errors accordingly (some systems with huge transactions per second have successfully used the error response method, instead of using locks).

One thing to understand is that different combinations of lock hints implicitly alter the level of transaction isolation that affects what types of locks were acquired. This changes everything: what other locks are provided (for example, a simple read), the time when the lock is increased for updating from the intention of updating, etc.

I highly recommend that you read more information about these issues in race conditions. You need to do it right.

• Conditional enable / update race status
• UPSERT Race Condition with MERGE

Code example

 CREATE PROCEDURE dbo.PermitStatusUpdate @PermitIDs varchar(8000), -- or (max) @Status int AS SET NOCOUNT, XACT_ABORT ON -- see note below BEGIN TRAN DECLARE @Permits TABLE ( PermitID int NOT NULL PRIMARY KEY CLUSTERED ) INSERT @Permits SELECT Value FROM dbo.Split(@PermitIDs) -- split function of your choice UPDATE S SET UpdatedOn = GETUTCDATE(), Status = @Status FROM PermitStatus S WITH (UPDLOCK, HOLDLOCK) INNER JOIN @Permits P ON S.PermitID = P.PermitID INSERT PermitStatus ( PermitID, UpdatedOn, Status ) SELECT P.PermitID, GetUTCDate(), @Status FROM @Permits P WHERE NOT EXISTS ( SELECT 1 FROM PermitStatus S WHERE P.PermitID = S.PermitID ) COMMIT TRAN RETURN @@ERROR; 

Note: XACT_ABORT helps ensure that an explicit transaction is closed after a timeout or unexpected error.

To confirm that this fixes the lock problem, open several request windows and follow the identical sequence:

 WAITFOR TIME '11:00:00' -- use a time in the near future EXEC dbo.PermitStatusUpdate @PermitIDs = '123,124,125,126', 1 

All of these different sessions will execute the stored procedure almost at the same moment. Check each session for errors. If there are none, try the same test several times (since this may not always have a race condition, especially with MERGE).

The link entries I cited above provide even more detailed information than here, and also describe what to do for the SQL 2008 MERGE statement. Please read them carefully to really understand the problem.

In short, an explicit transaction is not required with MERGE, but you need to use SET XACT_ABORT ON and use the hint to lock:

 SET NOCOUNT, XACT_ABORT ON; MERGE dbo.Table WITH (HOLDLOCK) AS TableAlias ... 

This will prevent concurrency errors causing errors.

I also recommend that you perform error handling after each data modification application.

Answer 3

If you use SQL Server 2008, you can use the parameters evaluated by the table - you go to the records table in the stored procedure and then you can do MERGE .

Passing in the table value parameter will eliminate the need for parsing CSV strings.

Edit:
ErikE raised a question about race status, see His answer and related articles.

Answer 4

If you have SQL Server 2008, you can use MERGE . The article is described here .

Answer 5

You should be able to do your insert and your update in the form of two given requests.

The code below was based on a data loading procedure that I wrote some time ago that took data from a staging table and inserted or updated it in the main table.

I tried to bring it into line with your example, but you may need to tweak it (and create a table with a UDF score for CSV analysis into an identifier table).

 -- Update where the join on permitstatus matches Update PermitStatus Set [UpdatedOn]=GETUTCDATE(), [Status]=staging.Status From PermitStatus status Join StagingTable staging On staging.PermitId = status.PermitId -- Insert the new records, based on the Where Not Exists Insert PermitStatus(Updatedon, Status, PermitId) Select (GETUTCDATE(), staging.status, staging.permitId From StagingTable staging Where Not Exists ( Select 1 from PermitStatus status Where status.PermitId = staging.PermidId ) 

Answer 6

Essentially you have an upsert stored procedure (e.g. UpsertSinglePermit)
(for example, the code that you specified above) to work with a single line.

So, the steps that I see is to create a new stored procedure (UpsertNPermits) that does

a) Parse the input string into n records of the record (each record contains an identifier and permission status) b) The previous record above, call UpsertSinglePermit