CODEWITHSUNDEEP
sqlsql-servert-sqlmergebatch-processing
Just a learner
Just a learner

Reputation: 28612

Is it possible to do batch operations with TSQL "merge" statement?

With a simple UPDATE statement we can do it in batches when dealing with huge tables.

WHILE 1 = 1
BEGIN
    UPDATE TOP (5000)
            dbo.LargeOrders
    SET     CustomerID = N'ABCDE'
    WHERE   CustomerID = N'OLDWO';

    IF @@rowcount < 5000
        BREAK;
END

When working with MERGE statement, is it possible to do similar things? As I know this is not possible, because you need to do different operations based on the condition. For example to UPDATE when matched and to INSERT when not matched. I just want to confirm on it and I may need to switch to the old-school UPDATE & INSERT if it's true.

Upvotes: 0

Views: 2220

Answers (1)

Randy Minder
Randy Minder

Reputation: 48512

Why not use a temp table as the source of your MERGE and then handle batching via the source table. I do this in a project of mine where I'll batch 50,000 rows to a temp table and then use the temp table as the source for the MERGE. I do this in a loop until all rows are processed.

The temp table could be a physical table or an in memory table.

Upvotes: 2

Related Questions