Best way to run a large number of update queries by 1000 queries at a time

user_3067761 0
Viewed 423

I have a SQL Server file with 50,000 update queries like this:

UPDATE [dbo].[TABLE1] SET [COLUMN2] = 'abc123' WHERE [Id] = 100;
UPDATE [dbo].[TABLE1] SET [COLUMN2] = 'def456' WHERE [Id] = 101;
UPDATE [dbo].[TABLE1] SET [COLUMN2] = 'ghi789' WHERE [Id] = 205;
UPDATE [dbo].[TABLE1] SET [COLUMN2] = 'jkl012' WHERE [Id] = 216;
UPDATE [dbo].[TABLE1] SET [COLUMN2] = 'mno345' WHERE [Id] = 350;

I don't want to run this file and execute all 50,000 queries at once. What is the best way to do this by executing 1000 queries at a time, and when these are done move on to the next 1000 until all are executed? Also If I get an error how would I do a rollback?

tsql sql-server sql
3 Answers

You should create a temporary table with the values. You can then use this for the update. Absent that, you can create a derived table in the query:

with t as (
      select v.*
      from (values (100, 'abc123'),
                   (200, 'def456'),
                   . . .
           ) v(id, column2)
     )
update t1
    set column2 = t.column2
    from dbo.table1 t1 join
         t
         on t1.id = t.id;
user_11440350

use CASE WHEN statement

UPDATE [dbo].[TABLE1] 
SET    [COLUMN2] = CASE [Id]
                   WHEN 100 THEN 'abc123'
                   WHEN 101 THEN 'def456'
                   WHEN 205 THEN 'ghi789'
                   WHEN 216 THEN 'jkl012'
                   WHEN 350 THEN 'mno345'
                   END
WHERE  [Id] IN ( 100 , 101, 205, 216, 350 )
user_1856470

You can try to use UNION ALL create a result set and write row number for it then do UPDATE .... JOIN.

What is the best way to do this by executing 1000 queries at a time?

If you want to update date for 1000 batch you can try to use loop (while) with variables to control. 

DECLARE @fromNum int = 1;
DECLARE @toNum int = 1000;
DECLARE @totalRowNum int;

;with cte as (
    SELECT 'abc123' COLUMN2,100 ID
    UNION ALL
    SELECT 'def456'        ,101 
    UNION ALL              
    SELECT 'ghi789'        ,205 
    UNION ALL              
    SELECT 'jkl012'        ,216 
    UNION ALL              
    SELECT 'mno345'        ,350 
), cteRowNum as(
    SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT ID)) rn
    FROM cte
)
SELECT *
INTO #Temp
FROM cteRowNum

SELECT @totalRowNum = MAX(rn) 
FROM #Temp

While(@toNum < @totalRowNum)
BEGIN
    BEGIN TRY
        BEGIN TRAN
        update t1
        set t1.COLUMN2 = t2.COLUMN2
        from TABLE1 t1 join #Temp t2 on t1.id = t2.ID 
        where t2.rn between @fromNum and @toNum
        COMMIT TRAN
    END TRY
    BEGIN CATCH
    　ROLLBACK TRAN
    　SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
    END CATCH

    SET @fromNum = @toNum
    SET @toNum = @toNum + 1000
END

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
DROP TABLE #Temp
user_51760710
Related