Questions

Update data from one table to another table

Tags:
+
0 Votes
Locked

Update data from one table to another table

greg_gordon
Greetings:

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.
  • +
    0 Votes
    Tony Hopkinson

    Truncate Table OutOfDate
    Insert OutOfDate Select * From UpToDate

    +
    0 Votes
    greg_gordon

    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.

    +
    0 Votes
    Tony Hopkinson

    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

    +
    0 Votes
    greg_gordon

    Tony:

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

    Greg

  • +
    0 Votes
    Tony Hopkinson

    Truncate Table OutOfDate
    Insert OutOfDate Select * From UpToDate

    +
    0 Votes
    greg_gordon

    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.

    +
    0 Votes
    Tony Hopkinson

    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

    +
    0 Votes
    greg_gordon

    Tony:

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

    Greg