Copy a row into same table with most original values
709066Nov 17 2009 — edited Nov 18 2009Hello SQL experts I hope you can help me out. Forgive me if this topic has already been answered and posted but I could not find it.
I need to copy many thousands of rows in a table to itself (same table). The purpose is to insert/append an new effdt row for non-respondents from a recent survey. I keep getting the old "ORA-00001: unique constraint" error when I try to do run sql. If I specify a value for one of the key fields, emplid, it is no problem but I cannot specify 4,000+ values in my sql.
Would you please give me some direction on how to accomplish this? Your help is greatly appreciated. Here is my test SQL that does not work:
INSERT INTO pers_data_usa A
(effdt,emplid,MILITARY_STATUS,US_WORK_ELIGIBILTY,
CITIZEN_PROOF1,CITIZEN_PROOF2,MEDICARE_ENTLD_DT)
SELECT TO_DATE(SYSDATE,'DD/MON/YYYY') EFFDT,B.emplid,
'1','Y',B.CITIZEN_PROOF1,B.CITIZEN_PROOF2,B.MEDICARE_ENTLD_DT
FROM pers_data_usa B, pers_data_usa A
WHERE A.EMPLID = B.emplid
AND A.EFFDT <> SYSDATE
ORA-00001: unique constraint (SCHEMA.PERS_DATA_USA)
When I put values in emplid, the row is created. There is a unique constraint on emplid for not null and it is a key field.
Thanks in advance for your responses and consideration.....Jason