I often use DTS to move databases between the servers. When you choose "Transform" data and Column Mappings and Transformation window opens, the default option is Append rows to destination table.
Question: Is there a way to change the default option to Delete rows in destination table, so I wouldnt have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
ThanksPreceed the step with either DELETE or TRUNCATE script that you can easily generate yourself.
create view dbo.vw_DELETE_ALL_TABLES as
select 'DELETE ' + db_name() + '.dbo.[' + name + '] ' + char(13) + char(10) + 'go'
from dbo.sysobjects where objectproperty(id, 'IsTable')=1
and objectproperty(id, 'IsMSShipped')=0
Below is the OS step that you will need to create:
bcp <you_db>.dbo.vw_DELETE_ALL_TABLES out DELETE_ALL_TABLES.SQL -S <your_server> -T -c
osql -S <your_server> -E -i DELETE_ALL_TABLES.SQL|||Thanks rdjabarov,
It might work if you are dealing with a whole database.
But what if user asks you to refresh development database with the production data except a few tables? In this case you will need to modify your script, or ...?
That is why I was wondering if I could modify the default setting for DTS to "Delete rows in destination tables".
Thanks again|||Well, then have an exception table and populate it with tables that don't need to be refreshed. Then modify the view to return only records that are not found in the exception table. Easy, right? ;)|||It might be easy if you don't have 230 databases to support.
In my situation maintaining "exceptions" table in each database can be entire new project. So, I guess I will do it the old way - open and click to transform each table.
Thank you for your help.|||Well what I would do is create a quick web app that will pass the parameters such as what tables to transform and weather to delete the data or not to the dts package and then execute it.|||It might be easy if you don't have 230 databases to support...I used to support 1500 databases, now I support over 3500. If you think you can't afford to invest 1.5 hours in designing the structure and the process that would take care of it...I rest my case ;)|||1500? How many servers?
And that sound ludicrous...DTS..
I would dump and RESTORE...|||Calm down guys,
You forgot my initial question: Is there a way to make a default one of the DTS options in "Column mappings and transformations" window to "Delete rows in destination table" instead of "Append..."? That's it.
I'm pretty sure there are dozen of ways to transfer databases/data between the servers, as well as dozen of reason why you prefer to choose one way or another.
Thanks a lot for your help
No comments:
Post a Comment