September 9, 2010 at 5:56 pm
Hi,
I need to archive few tables from my database and ensure that I don't delete new records, which have not been archived.
I want to implement something like this:
Begin tran
insert into mydb_archive..mytable
select * from mydb..mytable
truncate mydb..mytable
Commit tran
Would this be sufficient?
Thanks.
September 9, 2010 at 7:15 pm
This isn't quite robust enough. If new rows are inserted during your SELECT operation, you may or may not end up with them in the archive table, which means they will be truncated.
If you lock the entire table (TABLOCKX, HOLDLOCK), you can prevent new rows from coming in. Unfortunately, any insert operation will be blocked until the transaction finishes. This could cause application timeouts.
If this is going to be a constant operation, I'd suggest a process where you archive and delete (not truncate) a small set of rows at each time.
If this is a once-off operation, you might try doing a quick switch - create a new table identical to mytable, and perform a rename. From that point, you new table will be empty, and you can archive the other table into your archive database at your leisure. This option won't work if you have referential intregity, but then, the truncation wouldn't work either.
September 14, 2010 at 7:41 am
one option is to archive all the rows which have been inserted till yesterday...
September 14, 2010 at 7:50 am
you need to define the archiving strategy first.
1. define the tables wants to archive
2. what would be the archiving period.
Abhijit - http://abhijitmore.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply