CODEWITHSUNDEEP
sql-serveropenquery
pthfndr2007
pthfndr2007

Reputation: 103

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

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

Upvotes: 1

Views: 1470

Answers (2)

elizabk
elizabk

Reputation: 480

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

Upvotes: 0

Travis Heeter
Travis Heeter

Reputation: 14084

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.

Upvotes: 1

Related Questions