Update data from one table to another table

By greg_gordon ·

I have two sql 2000 databases, one on one server and one on another server. After bringing in the data from one table, I would like to update the other database table with the changed data.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Quick and dirty

by Tony Hopkinson In reply to Update data from one tabl ...

Truncate Table OutOfDate
Insert OutOfDate Select * From UpToDate

Collapse -

Update data from one table to another table

by greg_gordon In reply to Quick and dirty

Thank you for your help. Perhaps I need to be more specific:

I have two databases with the same tables; one in the DMZ and one behind a firewall. I want to update data from either database based on their timestamp. Example:
Update VolunteerInfo table with ImportVolunteerInfo table via DTS package to schedule nightly run. The glue is an ID field being the same in both tables.

Collapse -

Two Way ?

by Tony Hopkinson In reply to Update data from one tabl ...

Big Problem is Deletes.
Normally you would do a delete where not exists or some such. Obviouls newly created records in one table won't exist teh other, nor will newly deleted.

Doing an update would be hideous on a wide table.
What about if two different columns have been updated on the same record in the two different databases?

You would have no way of knowing whether a change was required or not as one could have undone the other change, or if lookups are involved a change to one column could impact others that could then be overridden.
You could go for immediate record by record transfers, but you'need to go for remote updates and MSDTC transaction logging and cross your fingers for no locking issues.

New records but for delete would be relatively easy

I'd try to design this out , common database under a web service or some such.

Given that's not an option about the only way I can se of sensibly doing this would be some sort of replication strategy.

Instead of using the data itself, log (or immediately remotely execute) the SQL statements that result in the change and then apply them in time order to each other.

Even then you could easily get some funnies, and end up with various updates that you dare not do.

Update of an item one system after a delete on the other for instance.

Between us we could work out some SQL to do this but it's well flakey as a solution.

Insert Select * Where nit Exists basically, but for potential delete issue

Collapse -

Thank you

by greg_gordon In reply to Two Way ?


Thank you. I really appreciate your help. Would like to compare notes sometime.


Back to Networks Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums