nsert

[83]Thanks Jan for the reply... I have created a ba...... Tso P Jan 20,
[85]Thanks Jan My apologies...for not providing mo...... Tso P Jan 21,
[88]Interesting solution....it makes me wonder however...... Tim Boles
09:54

Subject: insert into statement
Os info: RHEL5
Oracle info: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
- Production
Message: Hi Guys
I am running a insert into table using the following:
insert into table_x select * from table_backup where creation_date <=
ADD_MONTHS(TRUNC(SYSDATE), -3);
ERROR at line 1:
ORA-00001: unique constraint (schema.UDX_CHECKSUM) violated
How can I modify the SQL so that a duplicate value is not created?
Thanks in advance...
Please Help!!!!


Subject: Re: insert into statement
Message: Hi Tso
Your question reads to me like this:
I'm trying to put something into something else, but it won't fit. Why?
I have no idea what you are putting in (no information about the data).
I have no idea what you are putting it into (no information about the
target table).
I have no idea what's already in there (no information about the
contents of the target table).
So, how is anyone supposed to help you?
Regards,
Jan


Subject: Re: insert into statement
Message: Thanks Jan for the reply...
I have created a backup of a target table called it table_archive using
CTAS.
We delete data from that target table based on the creation_date
column.
Now, I am trying to insert the data back to the target_table from the
backup_table and I get this constraint violated error.
I need help in inserting data back to target_table maybe using the
procedure and avoid the duplicates.
If I get help even maybe with the syntax for the procedure to perform
this task using :
exception
when DUP_VAL_ON_INDEX then
NULL;
Thanks again...


Subject: Re: insert into statement
Message: Hi Tso
I'm sorry, but to say it bluntly: You are writing as if you have never
before asked a question in this forum.
I'm sure you have been told numerous times that we cannot guess at the
structures of your objects.
To know the reason for this issue I would need ALL the information
about the structure of the tables, indexes and triggers on those
tables, possibly foreign-key constraints to other tables, how (exact
statement) you created the backup-table, how (exact statement) you
deleted data, how (exact statement) you try to insert data, etc., etc.
Too many variables, too little data. Too little visible effort on your
side to provide the needed data to those you request answers from.
Regards,
Jan


Subject: Re: insert into statement
Message: Thanks Jan
My apologies...for not providing more information to this issue...
I have created a pl/sql procedure to do the insert and I handled the
duplicates with when DUP_VAL_ON_INDEX then
NULL;
I do have data now.
I really appreciate the reply though I could not provide more info.
Thanks again.


Subject: Re: insert into statement
Message: without the table structure, you might succeed using not exist
to check if the constraint won't be violated by the insert.
insert into table_x select * from table_backup where creation_date <= ADD_MONTHS
(TRUNC(SYSDATE), -3)
AND NOT EXISTS (SELECT 1 from table_x where x.primarykey=table_backup.tobeprimar
ykey)



Subject: Re: insert into statement
Message: Thanks Rob


Subject: Re: insert into statement
Message: Interesting solution....it makes me wonder however. How do you
know that the row exists contains the "correct" data?
I guess you could logically say the data in the table overrides any
previous data and is correct.


Subject: Re: insert into statement
Message: Wouldn't merge be an option ?