Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2005-06-22 : 16:45:19
|
| Hi,I am pretty new to DTS and was hoping someone could give a few pointers. I have two databases which contain about 50 tables that I need to transfer the data from one to the other. In order to maintain integrity, there is a certain order to the transfer. The table list doesn't change but the database name does. Our application toggles between databases so we alway have the previous version. Here is the idea:day 1: transfer data from dbA to dbBday 2: drop and recreate dbA. transfer data from dbB to dbAday 3: drop and recreate dbB. transfer data from dbA to dbBSo essentially, I need a stand alone DTS program that can transfer data between the two databases (maybe the names are passed in as parameters?). I'm not quite sure how to approach this problem (in the past the only way I have used DTS is through enterprise manager in an ad hoc fashion). This would be more of a permanent solution. Any suggestions on the best approach would be greatly appreciated.Thanks,NicNic |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 18:54:23
|
| I wouldn't even bother with DTS for this. Just use T-SQL scripts. You can get to the other database's tables using the three part naming convention: DbName.OwnerName.ObjectName.So to transfer data using T-SQL:INSERT INTO DbName.OwnerName.ObjectName (Column1, Column2, ...)SELECT Column1, Column2, ...FROM ObjectName...Tara |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2005-06-23 : 11:46:06
|
| Thanks for the suggestion. The amount of data is large. Some of the tables could have 100,000+ records. I'm looking to automate this process. Do you see problems with inserting that many records with one statement? Since I need to move 50+ tables, would adding a "go" statement at the end of each insert be a good idea?Nic |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-23 : 11:55:18
|
| The T-SQL way can be automated. You don't need DTS to automate things. Just bundle your scripts up into a file and stick it in a job.I would do this in batches so that you don't affect other users using the systems. Check this out:http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspxA GO statement doesn't really help here. Committing the data and checkpointing it between batches or tables is a good idea.The link above is for a controlled delete, but it can easily be modified for an INSERT. We use this approach all of the time for purging. We run it in the middle of the night, but we still don't want to impact anyone who might be using the system. So we do things in batches to release the locks on the tables. It makes the process go much slower, but that's what we want.Tara |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2005-07-21 : 16:27:45
|
| I don't get why you want to keep going back & forth .... but ....You could make 2 DTS packages, and run each one every other day. Each one truncates the data & copies the new data over. |
|
|
|
|
|
|
|
|
creating stand alone DTS package