I have an unusual situation... For testing purposes, I copy production to a test database and then need to prune the test database of the majority of the data.
Since I cannot use a WHERE clause on a Truncate, I have been using DELETE statements, but these take a long time and fail if I try to do too much at once since the log files get too large.
I would like to copy the data I need to preserve to a backup table, truncate the original table and then load the preserved data from the backup table into the original one.
1. The tables all have timestamp type fields which seem to require that the fields be specified when backing up the table. There are a LOT of tables potentially, so I am loath to do this, though it is certainly possible to write a script to generate the sql code to do it dynamically and then just execute the scripts.
2. Many of the tables have constraints, foreign keys, triggers etc on them and I naturally need to preserve these, or at least save them and recreate them once the update is complete.
Any ideas ?