• Oh, missed that. Since your only concern was the locking on the source table, not on the destination table.

    Alternative #1 - use an intermediate table. Do the select on the source and insert into an intermediate table with no constraints and only a clustered index. Then as step 2 insert from that table into the destination table.

    Alternative #2 - just like alternative #1, but add batching to the second step so that the destination table is also not locked for prolonged periods.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/