August 10, 2007 at 11:24 am
I have a requirement to archive certain tables from production database to an archival database. And delete those rows from Prod
It involves a complex relation of tables with RI.
The archival DB has no RI but some primary identity keys to avoid duplicate inserts. However i would like to design the archival assuming RI is in place.
Please let me know if you have any suggestions.
August 11, 2007 at 2:49 am
Apart from the absence of referential integrity (in long form for newbies), are your prod and arch table structures identical? Are you copying across all the data or filtering it out? And what method are you using to do the copy across?
August 13, 2007 at 6:31 am
Yes, the prod and arch table structures are similar.
I am copying across data which would fit certain criteria (one being the date for instance).Not that every table has a date column. But a custom query should yeild me what i need to archive
I am trying to figure out what to use DTS/bcp etc. Since we have plenty of tables and i dont want to be doing a table to table map for each transfer task (is i use DTS)
Please let me know your suggestions too..!
August 13, 2007 at 8:47 am
How else will you do it other than mapping each table? There's not facility for automatically moving tables to an archive.
Set up a task in DTS for each table, use a proc to get the data and move it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 13, 2007 at 10:54 am
Thanks for the suggestion.
I had some questions on how to do error handling. I am not very concerned on the inserts into the archival db as its pretty much going to be constraint free.
But if i hit an error on clean up of the prod tables ; how should my error handling logic be detailed out?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply