March 27, 2008 at 10:57 am
I have two tables where I need to move some billions of rows to some another backup tables on the same database.
assume, I have Server A --> Database D --> Table T1, T2, T1Backup, T2 Backup
now I need to copy some very old data to T1Backup from T1 and T2Backup from T2 and remove those rows from T1 and T2.
Earlier, I wrote a SP to do this but while removing data from main tables, it took ages.
Can anyone please advise me on the SQL statements or SP that I need to create for this?
Thanks in advance,
Sarath
March 27, 2008 at 11:38 am
Hi Sarath,
Do you already have the structure for the 2 backup tables created? If so then this should be a simple
INSERT INTO TABLE_BACKUP(column1, 2 ...) SELECT column1, 2 ... FROM TABLE WHERE <--- the where criteria is what you need to make sure is correct. Now you say there are billions of rows that need copying. I would first make sure you check the Server disk space, database growth options, and resources to make sure you have available resources. If there is a way for you to break up your WHERE criteria into even smaller segments that might be a good idea.
After this you would do a DELETE FROM TABLE with the same exact WHERE criteria as your select. As always make sure you have a good backup in place before any large table changes.
Hope that helps
March 27, 2008 at 11:08 pm
Thanks for your reply..
Last time I did the same for lakhs of records... but when I ran the query DELETE FROM TABLE WHERE .... , it took ages for me to renove the data;
My question is --- is there any better way to remove data efficiently? Suppose, I want to remove entries before 2007, do I need to write multiple DELETE queries say for 2005, 2006 separately... What is optimal no. of rows that we can try to remove at a time?
DELETE FROM TABLE WHERE DATE < 01/01/2007... i can't run this query... is there anyway to create any SP with argument passing 2007 and deletes automaticallly all the data in an efficient and fast manner?
How would the following approach?
i'll put rowcount to be affected 2000 and then run the above query in a loop, till all the rows get deleted; can you tell me if yo have any ideas on this?
Thanks again,
Sarath
March 28, 2008 at 7:16 am
It may be quicker to do something like the following:
1) SELECT * INTO T1BackupNew FROM T1 - this'll create a total copy of your table
2) truncate table T1 - faster than delete and less logging
3) Insert into T1 select * from T1BackupNew where DATE > 12/31/2006 - gets all your required data abck into your table
4) Insert into T1Backup select * from T1BackupNew where DATE < 01/01/2007 - inserts your archive data
5) drop table T1BackupNew - cleanup
If you have lots of data prior to 1/1/07, my guess is your dataset for 2007 and forward is going to be substantially smaller and this process should be faster.
-- You can't be late until you show up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply