I am going to
migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use
DELETE with OUTPUT clause, but need to have a
buffer table instead.
data source table is not going to be read by other users, but the
new table (in which I am inserting records will be frequently used during the operation) - so, I need to do the migration without blocking the other users activity.
The database is under
FULL recovery model,
SQL Server 2012 and transaction log backup is made on every
15 minutes (the transaction log growth during the operation is not an issue anyway).
I have the following
CREATE TABLE #DataBuffer ( .... ); DECLARE @RowCount INT; WHILE 1 = 1 BEGIN BEGIN TRY BEGIN TRANSACTION TRUNCATE TABLE #DataBuffer; DELETE TOP (4000) FROM [dbo].[OldSourceTable] OUTPUT [deleted]. ... INTO #DataBuffer (...) WHERE ... ; INSERT INTO [dbo].[NewSourceTable] (...) SELECT ... FROM #DataBuffer; SET @RowCount = @@ROWCOUNT; COMMIT TRANSACTION IF @RowCount = 0 BEGIN BREAK END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION END END CATCH END
and I am wondering which is the best value of rows to be deleted in a batch delete statement?
I have seen different variants and the only thing I have found as recommendation was from here:
If you delete 4000 or more rows in a single command, the whole table will be locked (or the whole partition if you enabled that on partitioned table). DO NOT delete 10000 rows in a batch if the table needs to be available for other users! So, delete e.g. 2000 rows in a single delete...
But I was not able to find a confirmation in the
MSDN or elsewhere about this.