question

jdem avatar image
jdem asked

SQL Server batch update, user-defined Table types

i need to batch updates on a table from a stored procedure that is has user defined table types. The updates are causing table locking and i would like to mitigate that. I would like to batch them in updates of 1000. The code would look as below CREATE PROCEDURE [dbo].[updatestuff] @update1 AS udtupdate1 READONLY , @update2 AS udtupdate2 READONLY , @time DATETIME AS BEGIN SET NOCOUNT ON UPDATE lms SET lms.col1 = lsp.col1 , lms.TimeStamp = @time FROM dbo.tlivetable1 lms INNER JOIN @update1 lsp ON lms.livetable1Id = lsp.colId UPDATE ms SET ms.col1 = lsp.col1 , ms.TimeStamp = @time FROM dbo.tmrks ms INNER JOIN @update1 lsp ON ms.mrksId = lsp.Colid UPDATE mms SET mms.col2 = msp.col2 , mms.Date = @time FROM dbo.tMMSel mms INNER JOIN @update2 msp ON msp.col1Id = mms.MMId AND msp.col2Id = mms.MMSId END Any help would be really appreciated
t-sqlstored-proceduresupdate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
sjimmo avatar image
sjimmo answered
Try to wrap each of your update statements with: DECLARE @b INT=1 SELECT @b = WHILE( @b >= 1 ) BEGIN UPDATE TOP (1000) lms SET lms.col1 = lsp.col1 , lms.TimeStamp = @time FROM dbo.tlivetable1 lms INNER JOIN @update1 lsp ON lms.livetable1Id = lsp.colId SET @b=@b - 1000 END
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.