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
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
19 People are following this question.
