How do I generate Conditional TRUNCATE statements ?

• simonholzman 14059

  SSC-Addicted

  Points: 459

  More actions

  May 16, 2013 at 11:22 am

  #277689

  I regularly copy our Production database to our Test system and then need to delete most of the data, while keeping sufficient to allow meaningful testing.

  I generate a list of record keys that should be kept and then I have been using multiple

  DELETE FROM <table>

  WHERE <key> NOT IN (SELECT <key> FROM <keep table>)statements to prune the various tables in the database.

  This is slow and generates logfiles which kill the system if I try to run too many of the deletes in a single execute.

  What I would like to do is effectively;

  For each <Table>

  SELECT *

  INTO Temp_<Table>

  FROM <Table> WITH (nolock)

  WHERE <key> IN (SELECT <key> FROM <Keep Table>)

  TRUNCATE TABLE <Table>

  INSERT INTO <Table>

  SELECT *

  FROM Temp_<Table> WITH (nolock)

  Of course, this does not actually work.:-P

  Firstly, all of the tables have timestamp type fields which the asterisk cannot handle. However, since this is potentially using a dynamic list of the tables, it should be easy enough to dynamically list the fields as well, generating a SQL Statement that then gets executed.

  :ermm:Is there an existing script to copy multiple tables to another name ?

  The other issue is that most of the tables have triggers and constraints on them, including foreign keys. I am not sure;

  :ermm:What needs to be dropped or disabled to allow this process to run without any automatic integrity management processes running

  :ermm:How to drop or disable these processes and then re-enable them once update is complete so that the system will run the application normally.

  Any ideas or suggestions as to where to look for this information ?