-
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 ?
-
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.
-
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
-
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 ?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|