delete large amount of data

up vote0down votefavorite

i have to delete a large amount of data. truncate is not possible because of relations. And I don't wanna drop the table because of views. I am using code below but is there better idea? delete from table WHILE 1=1 BEGIN BEGIN TRAN DELETE  top (1000000) from table IF @@rowcount < 1000000 BREAK WAITFOR DELAY '00:00:00:010' COMMIT end  DELETE  from table tsql link|edit|rollback|delete|flag edited Sep 2 at 14:32 Kirk Woll 8,1011820 asked Sep 2 at 14:04 MahmutHAKTAN 325 75% accept rate

Do you have a question? – Neil N Sep 2 at 14:07 add comment

2 Answers

activenewestvotes

up vote0down voteaccept

Truncate table would be the best by far. If you're worried about relationships because of the referencial integrity, than I suggest you update separately those tables that reference it by foreign key (e.g. if foreign keys have ON DELETE SET NULL then UPDATE RefTbl SET Key = NULL WHERE Key IS NOT NULL, similarly, delete if cascade etc...). Alternatively, if you don't want that, you can alter your query to be a bit quicker by using ROWCOUNT: --other code for loop SET ROWCOUNT 1000000 --this limits the number of rows that will be processed DELETE FROM Table1 Also, I'm not sure why are you doing this inside a transaction? It would create a huge transaction log and impact your performance severely... When you're performing stuff like dumping table content usually transactions are not relevant (it could be relevant in your case, but I'm just saying it's rarely the case). If you do need transaction, than at least move your delay out of it... link|flag answered Sep 2 at 15:11 veljkoz 1,298417

enter at least 15 characters

up vote0down voteaccept

You could do it a bit simpler: delete table where id in (select top 1000000 id from table) link|flag answered Sep 2 at 14:50 Shiraz Bhaiji 13.8k2737