How to update a SQL Server table with updated Oracle table Data

Question

I am using SQL Server (and Oracle as a linked Server).

The task involves updating the SQL Server table with any new changes to the column NAME in the Oracle TABLE.

EMP is PK and NAME is the column that can change.

ORACLE TABLE = TABLE1:

EMP                  NAME                
-------------------- --------------------
1                    JOE               
2                    David               
3                    BOB

SQL SERVER TABLE = TABLE1:

EMP                  NAME                
-------------------- --------------------
1                    JOE               
2                    David               
3                    FRED

Code which is not working (runs and runs w/o updating):

 UPDATE OC 
    SET    OC.NAME = MS.NAME
    FROM   OPENQUERY(ORCL12,
      'select EMP, NAME from TEST1') OC 
         INNER JOIN [dbo].[TEST1] MS 
     ON MS.EMP = OC.EMP

You're updating the Oracle server `OC` instead of `MS`. – elizabk Feb 25 '19 at 20:08 — elizabk, Feb 25 '19 at 20:08
tweaked the code and it is working, thank you! – pthfndr2007 Feb 25 '19 at 20:51 — pthfndr2007, Feb 25 '19 at 20:51

score 1 · Accepted Answer · answered Feb 25 '19 at 20:57

1

Thanks to @elizabk, here's the answer.

UPDATE MS 
    SET    MS.NAME = OC.NAME
    FROM   OPENQUERY(WZ12,
      'select EMP, NAME from Test') OC 
   INNER JOIN [dbo].[TEST1] MS 
     ON OC.EMP = MS.EMP

Note: This was originally an edit to the original question. I just moved it here. @elizabk if you want to post your response, I'll delete this one.

answered Feb 25 '19 at 20:57

Travis Heeter

13,002
13
87
129

I posted my answer – elizabk Feb 26 '19 at 01:26

score 0 · Answer 2 · answered Feb 26 '19 at 01:26

0

You're updating the Oracle server OC instead of the SQL Server table MS.

UPDATE MS 
    SET    MS.NAME = OC.NAME
    FROM   OPENQUERY(WZ12,
      'select EMP, NAME from Test') OC 
   INNER JOIN [dbo].[TEST1] MS 
     ON OC.EMP = MS.EMP

answered Feb 26 '19 at 01:26

elizabk

480
2
11

OP needs to accept your answer. You're not allowed to delete an accepted answer.. @pthfndr2007 – Travis Heeter Feb 26 '19 at 14:08

How to update a SQL Server table with updated Oracle table Data

2 Answers2