sql statement problem

[pascal01]

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 ?

[PAVB]

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.

[LKBrwn_DBA]

If the "num" column is unique (or primary) key, you could try:

INSERT INTO cause
   SELECT *
     FROM cause@dblink b
    WHERE NOT EXISTS (SELECT '?'
                        FROM cause a
                       WHERE a.num = b.num);

[pascal01]

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 ?

[PAVB]

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.

[bobanjayan]

How about export the tables from one database and import those with parameter ignore=Y to the other database..

Regards.

Boban