Bulk Delete

Question

Bulk Delete

The Natrix

SSC Eights!

Points: 835
More actions
March 19, 2010 at 9:21 am

#218420

Hello,
I have a table with over 20 million records I want to delete all the data except the most recent 2million. Is there a 'quick' way to do this? I was thinking of inserting the records I want to keep in a temp table then deleting everything and transferring the data from the temp table back to the original. But I got quite a few timeouts while trying to do this for about 7mins. I'd like to know if there's a better way to go about it. Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

move the records you want to retain in other table using import and export and drop the table.......and restore the design and structure of new table as similar to old one....

----------
Ashish

The Natrix SSC Eights! Points: 835 More actions · Answer 2

I can't drop the table because this is to be done on the live database, which constantly has rows being inserted. If dropping the table takes ten minutes I wouldn't have a record of new rows inserted during that time.

Thanks.

Paul White SSC Guru Points: 150467 More actions · Answer 3

This topic has just been discussed in detail in another thread:

http://www.sqlservercentral.com/Forums/Topic885718-360-1.aspx

It is pretty comprehensive.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Natrix SSC Eights! Points: 835 More actions · Answer 4

Sorry I misread what you said, disregard my previous post...

The Natrix SSC Eights! Points: 835 More actions · Answer 5

The Natrix

SSC Eights!

Points: 835

March 19, 2010 at 10:24 am

#1136872

Thanks for the reference Paul, I'll check it out

crazy4sql SSCoach Points: 19590 More actions · Answer 6

what are the condition based on which you deciding for the data to be retained?

----------
Ashish

Paul White SSC Guru Points: 150467 More actions · Answer 7

ashish.kuriyal (3/19/2010)
what are the condition based on which you deciding for the data to be retained?

Let him read the other thread, and the article referenced there. No doubt that will give him some ideas 🙂

link to the article: http://www.sqlservercentral.com/articles/67898/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Natrix SSC Eights! Points: 835 More actions · Answer 8

I want the most recent 2million records to be retained. I'm using a timestamp to check.

Paul White SSC Guru Points: 150467 More actions · Answer 9

ncodner (3/19/2010)
I want the most recent 2million records to be retained. I'm using a timestamp to check.

Do you mean you have a date/time column or a timestamp (aka rowversion) column?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vidya_pande SSCertifiable Points: 5275 More actions · Answer 10

http://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx#bm884650

one more similar thread

The Natrix SSC Eights! Points: 835 More actions · Answer 11

The Natrix

SSC Eights!

Points: 835

March 20, 2010 at 6:44 pm

#1137281

I have a date/time column

Paul White SSC Guru Points: 150467 More actions · Answer 12

Natalie C (3/20/2010)
I have a date/time column

That will be fine then. You can use the technique in the article to gradually delete records in situ, or to copy rows to be saved incrementally. Either approach will work.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi