I have a production database which is SQL Server 2000 and I have a SQL Server 2005 database which is a copy of this database taken about a month ago.
I would like to set up a scheduled job to run every morning that will update the data (and only the data) in the 2005 database so it matches the production data.
I've been trying to create a package using the Import Wizard but the problem I'm having is that I would like to use the "Delete Rows in Destination Table" option which is set by clicking the edit button in the mappings column of each table. Problem is I have over 1000 tables and i'm not too keen on going through them all.
Is there any easy way to do what I want, bearing in mind that I can't alter anything on the production database I can only copy the data from it.
ThanksSo you want to delete all rows in a number of tables, correct?
Why not just write a T-SQL batch that uses a cursor to loop over all your tables, builds a dynamic SQL statement which is "TRUNCATE TABLE " + @.table_name, then executes that dynamic SQL.
If you want to run this from SSIS you can put it into an Execute SQL Task.
If you want to use SSIS to do the looping then that is easily possible using a Foreach loop. Let me know if you want to know more.
-Jamie|||
There are several programatic ways to do this but a more "straightforward" way would be to save the package. Add a SQL Task with a precedence constraint to run before the dataflow task that deletes all the data in all the tables. You can use SSMS to script the deletes for all the tables, although you have to do it in a round about manner. Select all the tables and then script. You will only be allowed to script create or drop. Script the drop and then remove all the script except for the drops commands themselves (i.e. there will be constraint altering, etc depending on how the tables where created). Then use Search and Replace to change the DROP TABLE to DELETE FROM.
HTH,
Matt
No comments:
Post a Comment