Post reply

Q: How would I update a table based on changes in an Oracle Linked Server view

  • January 9, 2011 at 11:38 pm

    #229922

    I'm looking for a solution to update a SQL table based on the changes in an Oracle Linked Server view. Naturally, I need to support the following:

    - If it's a create and it doesn't exist in the destination table, create a new row and update the destination table with the values from the source

    - If it's a create and the object exists in the destination, update the destination

    - If its an update in the source and the object doesn't exist in the destination, create a new row and update the destination table with the values from the source

    - If it's an update and the object exists in the destination, update the destination

    I'll handle deletes separately, basically it will set a flag in the destination and nothing more.

    Thanks in advance!

    Sincerely,

    Adam

  • January 10, 2011 at 5:47 am

    #1271333

    At first glance this seems like a situation that the MERGE statement could handle.

    http://msdn.microsoft.com/en-us/library/bb510625(v=SQL.100).aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 10, 2011 at 5:50 am

    #1271335

    Koen (da-zero) (1/10/2011)

    At first glance this seems like a situation that the MERGE statement could handle.

    I agree with Koen, a MERGE statement would work perfectly...only problem I forsee is the size of the Oracle View...for a MERGE agaisnt a linked server, the whole remote table will get copied locally to temp db, then the merge takes place...if it's a MillionBillionRow Table, that'll take time and resources.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply