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 |
|
Kristen
Test
22859 Posts |
 Posted - 2005-07-11 : 09:58:24
|
| We have some generic stuff for moving data into our systems.For example, the client has a Name and Address system (in their accounts package). They want any updates to names [say] to be copied to our package.So ... we set up some Staging Tables (with similar structure to our application) in a DB on a server "near" their accounts package. Then we do periodic "Update ours where different to theirs" type querries.Typically their application will have an UPDATE DATE column, so we can just query stuff that has changed (doesn't mean it has changed in a column relevant to our application, of course)That query will have amended data in our transfer database, which is [converted to be] in the structure of the data we want, rather than mimicing their structure.So ... I now have a transfer database on their server and I want to copy some data up to ours.To not overrun the available bandwidth I only want to send changes to our DB.So ... we have an UPDATE DATE column in our transfer database tables, and an "Action" (Insert, Update, Delete type codes)My intention is to transfer "new/changed" data from the local transfer database to similar transfer tables on the remote server, and then from that transfer tables [at the remote end] into the actual tables.(Note: it is quite likely that there is no database-to-database connection available at this point, so the "upload to remote" is likely to be XML)I'm a bit concerned about robustness when a transfer fails - e.g. I have added a row to the local transfer database as "Insert", then the upload to the remote fails, then it gets changed to "Changed" - so now gets uploaded as a "Change" rather than an "Insert" - of couse the Remote end can be happy to do an INSERT or UPDATE depending on what exists, but I fear there are a number of other Gotchas I ought to plan for.All ideas welcome!Kristen |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-07-11 : 10:38:43
|
| Have a trigger on the table that looks at the row being modified on the source. interogate the changes, and if it's one you want, write it to a new table.Then have a batch process to bcp out the rows for a window, and ship the data to the destination server?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-11 : 13:14:15
|
| Are you meaning that the trigger would be on the Client Application's database - or on my Transfer database?Kristen |
|
|
|
|
|
|
|
|
Data Staging