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

Question

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?

Create a memory variable table with all the data you want to update. They to an update on a join of that table. See: https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — James A Mohler, Nov 09 '18 at 00:10
Could you please clarify why don't you want to run the whole file? I mean what is the reason, because knowing the reason will most probably drive to right answer. — Serg, Nov 09 '18 at 15:49

score 3 · Answer 1 · answered Nov 09 '18 at 00:17

3

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;

answered Nov 09 '18 at 00:17

Gordon Linoff

1,242,037
58
646
786

I was going to suggest the same, except use a temporary table and inserts, instead of a CTE – e_i_pi Nov 09 '18 at 00:19

score 2 · Answer 2 · answered Nov 09 '18 at 00:11

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 )

D-Shih · Answer 3 · 2018-11-09T22:35:10.423

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

This looks like it could work. I'm trying to test this, but I'm getting this error: Invalid object name 'cteRowNum' in the line from Table1 t1 join cteRowNum t2 on t1.id = t2.ID — user3067761, Nov 09 '18 at 20:18
@user3067761 I edit my answer you can try it, the problem is `CTE` can only use in next query so I use `select .... insert` into a temp table . — D-Shih, Nov 09 '18 at 22:36

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

3 Answers3