March 19, 2010 at 9:21 am
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!
March 19, 2010 at 10:09 am
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
March 19, 2010 at 10:19 am
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.
March 19, 2010 at 10:20 am
This topic has just been discussed in detail in another thread:
http://www.sqlservercentral.com/Forums/Topic885718-360-1.aspx
It is pretty comprehensive.
March 19, 2010 at 10:20 am
Sorry I misread what you said, disregard my previous post...
March 19, 2010 at 10:24 am
Thanks for the reference Paul, I'll check it out
March 19, 2010 at 10:26 am
what are the condition based on which you deciding for the data to be retained?
----------
Ashish
March 19, 2010 at 10:35 am
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/
March 19, 2010 at 1:14 pm
I want the most recent 2million records to be retained. I'm using a timestamp to check.
March 19, 2010 at 9:25 pm
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?
March 20, 2010 at 12:34 pm
March 20, 2010 at 6:44 pm
I have a date/time column
March 20, 2010 at 10:21 pm
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply