Most by the will to merge in the database A is called 'yyjg. Chinese_prescription' table updates to the database B called 'chinese_prescription' table, two tables of field name, data type, but the B table has set up A foreign key, inserted into the database to be updated now B chinese_prescription table, suggesting the ORA - 02291 error (violate integrity constraints), and then I used the log errors into err_a (' merge ') reject limit unlimited (before that have used dbms_errlog created called err_a error log table), but there is no data in the log, I write statement is roughly as follows:
The merge into chinese_prescription a
Using (select * from yyjg. Chinese_prescription) b
On (Amy polumbo rescribe_serial_no=p. rescribe_serial_no
And a.s ettlement_sn=b.s ettlement_sn
And A.O UTPATIENT_NO=B.O UTPATIENT_NO)
The WHEN MATCHED THEN
UPDATE the SET
A.E STATUS=B.E STATUS,
A.C LINIC_ORGAN_NAME=biggest LINIC_ORGAN_NAME,
A.C LINIC_DEPT_CODE=biggest LINIC_DEPT_CODE,
WHEN NOT MATCHED THEN
INSERT (A.E STATUS,
A.C LINIC_ORGAN_CODE,
A.C LINIC_ORGAN_NAME,
A.C LINIC_DEPT_CODE,
A.C LINIC_DEPT_NAME,
Amy polumbo ATIENT_LOCAL_ID,
Amy polumbo RESCRIBE_SERIAL_NO,
Amy polumbo RESCRIBE_NO,
Amy polumbo RESCRIPTION_NOTE_INF,
Amy polumbo RESCRIPTION_DRUG_PHARM_SIG,
Amy polumbo RESCRIPTION_CHECK_SIG
)
VALUES
(
B.E STATUS,
biggest LINIC_ORGAN_CODE,
The biggest LINIC_ORGAN_NAME,
The biggest LINIC_DEPT_CODE,
The biggest LINIC_DEPT_NAME,
p. ATIENT_LOCAL_ID,
P. RESCRIBE_SERIAL_NO,
P. RESCRIBE_NO,
P. RESCRIPTION_NOTE_INF,
P. RESCRIPTION_DRUG_PHARM_SIG,
P. RESCRIPTION_CHECK_SIG
)
Log errors into err_a (' merge ') reject limit unlimited;
(because the field too much and I just wrote a part, which is set to a foreign key field is CLINIC_ORGAN_CODE and PATIENT_LOCAL_ID
The two fields in the UPDATE I have join the UPDATE, only in the insert)
I want to ask the question:
1 according to the function, use the log errors into can record the error and skip continue. Why my err_a when tip ORA - 02291 no errors in the record?
2. Because some data may be in violation of the rules of foreign keys, in addition to change the database table structure of B, what method can skip over the problem of data, continue to execute the statement until the update done?
3. Whether can use SQL statements to write like a kettle of insert/update the function of the module, if you have that should be how to write?
[/color]For passing by the great god help to look at, one can answer my question, I am deeply grateful, baidu has been unable to save me now