Avatar of paulCardiff
 asked on

What the best way to remove records in bulk via .net

What the best way to remove records in bulk via .net, i.e. we have something called System.Data.SqlClient.SqlBulkCopy but is there anything comparible for deletes?

As i'll potentially be deleteing rows in their 100s of thousands and the data cant we grouped in a sql statement, not unless i've 100's of thouands of where clauses.

C#ASP.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Gorkem Yuksel

8/22/2022 - Mon
Nasir Razzaq

What criteria would you use to delete rows and where would that criteria come from?

I'm doing a bulk remove feature - so i give my code a csv of ids to remove - and then code deletes them in turn or in bulk?
udaya kumar laligondla

if your application uses a TableAdapter (or DataAdapter), the adapter's Update method will delete rows in the database that have a RowState of Deleted. call delete method of data row to be deleted and once you have all the rows deleted just call update() on the adapter. this will delete the rows from server  
Your help has saved me hundreds of hours of internet surfing.
udaya kumar laligondla

you can write a program which will read the record Ids from the CSV and mark the records as deleted in the adapter then run update()

one more dirty way is to contruct a SQL command like 'Delete from ABC where Id in ['a','b','c',.......] and sending the same to the SQL server but this will be slow if your table contains large amoutn of records and no index on ID.
Nasir Razzaq

Yet another not so perfect method is to loop through the IDs and keep executing SQLs in the loop.

Thanks udayakumarlm and CodeCruiser: - however adding the the where clause wouldnt work for me sorry as were talking about 100s of thousands, and i'm worried about the overhead on using the adapter approach - as this is fine for small numbers but not large.

So one possiblility is that i do it in batches, or another is yes like CodeCruiser said i do it one line at a time. So far my plan b was always to do it one at a time e.g. 500,000 individual deletes - however there must be a better way that this - as im worried about performance for all the above?

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
udaya kumar laligondla

one more solution may be to store all your IDs in a Table on the Server and deleting records from the source server like

delete from MainTable
where id not in (select id from temptable)

this will be faster
Nasir Razzaq

For deletes you obviously need to specify some criteria unlike inserts. This criteria would keep changing for each row in its based on ID. If the ID column on the server is Indexed then it should not be a big overhead.
The approach mentioned of creating another table and using that would add overhead of its own when you first insert all the IDs to the database and then delete based on them.
Gorkem Yuksel

ok.. the simplest way of doing this with the most minial amount of code is to create a temp table on the SQL side of things..

First, do a bulk load into the temp table with all of the records you want to delete (basically an import).  Then use the following command to delete all from the main table.

"delete from tbl_Main where id in (select id from tbl_Temp)"

then delete all records in the temp table..

"truncate table tbl_Temp"

Hope this helps,


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Can i do this by using a temp table as a variable in stored procedure say, Or will i literally have to create a new table in db?

Gorkem Yuksel

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.