sql statement problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sql statement problem

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Question sql statement problem

    Hi. I want to join 2 tables. The second table exists in another database. I have created a database link. The 2 tables have the exact same definition.
    I want to insert into table "CAUSE" from table "CAUSE". In other words, I want to insert missing rows from production to test. I cannot seem to get the correct syntax or proper method ? Please excuse me, but I have never coded before.

    insert into CAUSE A as select from CAUSE@dblink B where A.num <> B.num;

    Is the above correct ? Is there a better way ?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Your syntax is not correct.

    I would either use MERGE statement or have a inline view checking if there is a match on local table for each row on remote table; in the last case you should use PK or Unique Index columns to check for existance.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Talking Exists or not exists, that is the question.

    If the "num" column is unique (or primary) key, you could try:
    Code:
    INSERT INTO cause
       SELECT *
         FROM cause@dblink b
        WHERE NOT EXISTS (SELECT '?'
                            FROM cause a
                           WHERE a.num = b.num);
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Question

    Thank you the example worked perfectly.

    However I am getting a problem on a few tables with columns of LONG type.
    I am getting an ORA-00997 error. How can I get around this ?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by pascal01
    However I am getting a problem on a few tables with columns of LONG type.
    I am getting an ORA-00997 error. How can I get around this ?
    What's your Oracle version?

    Long term solution would be to convert LONG to LOB.

    Workaround would be to rely on exp/imp to move the offending tables.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Dec 2008
    Posts
    9
    How about export the tables from one database and import those with parameter ignore=Y to the other database..

    Regards.

    Boban

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width