0

I try to find some way to delete a lot of data from table using batch with progress of deleting.

To delete I use the following code:

select 'to set rowcount'
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) @table from @table
END

But it does not provide info about state of deleting. So I try to add this functionality.

static void Main(string[] args)
{
    var cmd = _connection.CreateCommand();
    cmd.CommandText =
@"declare @table table(id int)
declare @i int = 0

insert into @table values (1)
WHILE @i < 20
BEGIN
insert into @table
select * from @table
set @i = @i + 1
END

select 'to set rowcount'
WHILE @@ROWCOUNT <> 0
BEGIN
select 'new batch'
DELETE TOP (100000) @table from @table
END";
    var reader = cmd.ExecuteReader();
    do
    {
        reader.Read();
        Console.WriteLine(reader[0]);
    } while (reader.NextResult());
    Console.WriteLine("done");
    Console.ReadLine();
}

It works but the output data is returned in the end of execution. But I try to get new batch before processing of each batch. I don't want to separate each batch to separated SqlCommand. I feel it can affect performance.

Serg046
  • 1,043
  • 1
  • 13
  • 42
  • Instead of trying to batch the delete you could just count the number of rows left (`select count(1) from @table READUNCOMMITTED`) – Wagner DosAnjos Nov 11 '17 at 00:26
  • How can It help? My delete operation can take a lot of time. I need to provide progress to my app. – Serg046 Nov 11 '17 at 12:47

0 Answers0