#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    41
    Rep Power
    7

    Sqlplus copy insert/update?


    Hello, so my issue is that i have two identical tables from different DB's and I need to copy a row from db1 and insert into db2 if it does not exist or update it if it does(on the tables pk).

    This is what i was trying until i realized replace drops the whole table where i thought it dropped the row and inserted the new.

    Code:
    copy 
    from db1
    to db2
    replace table2
    using
    select *
    from table1
    where x = blah
    Thanks
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387
    Originally Posted by kochaloch
    Hello, ... Etc ...I need to copy a row from db1 and insert into db2 if it does not exist or update it if it does(on the tables pk)
    1) The COPY command is obsolete
    2) Use the MERGE command:
    Code:
    MERGE INTO MyTable@DB2 s
    USING MyTable u
       ON s.keycol = u.keycol
     WHEN MATCHED THEN
       UPDATE SET ....
     WHEN NOT MATCHED THEN
       INSERT (cols...) VALUES (s.cols...);
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0
    Create database link for remote database and then Use the MERGE command:
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    41
    Rep Power
    7
    Worked perfectly, Thanks guys.

IMN logo majestic logo threadwatch logo seochat tools logo