[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7180
  • Last Modified:

oracle insert ignore or skip double values for constraint primary key

Hi,

I have a question because I want to transfer data from an SQL Server system to Oracle. The transfer includes about 15 tables. I want just to skip or replace double primary key values in the destination database. Otherwise I get a key/constraint violation and my procedure is not executed and no rows are inserted. Assume I have about 1.000.000 rows per day I want to transfer and need an effective way to do the task.

Appreciate any suggestions

Thanks so far!
Nicole
0
neatwork
Asked:
neatwork
  • 3
  • 3
  • 3
  • +4
3 Solutions
 
johnsoneSenior Oracle DBACommented:
You can set the primary key constraint to deferrable.  I would not set it to initially deferred though.  I would have your transaction set the constraint as deferred and then you should be able to insert the record as a duplicate, but it must be corrected before you commit or you will get an error.
0
 
dportasCommented:
Use a MERGE statement instead of an INSERT or UPDATE.
0
 
paquicubaCommented:
This is what you need --> dbms_errlog.create_error_log
Example:

Drop table Tab1
/
Drop Table TAB1_LOG
/
Create Table Tab1(col1 number constraint col1_pk primary key, col2 number)
/
insert into Tab1 values(1,1)
/
insert into Tab1 values(1,1)
/
exec dbms_errlog.create_error_log('TAB1','TAB1_LOG','ALEX','USERS')
/
insert into Tab1 values(1,1) LOG ERRORS INTO TAB1_LOG REJECT LIMIT UNLIMITED
/
SELECT * FROM TAB1_LOG

 
Drop table Tab1 succeeded.

Drop Table TAB1_LOG succeeded.

Create Table succeeded.

1 rows inserted

Error starting at line 9 in command:
insert into Tab1 values(1,1)
Error report:
SQL Error: ORA-00001: unique constraint (ALEX.COL1_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
anonymous block completed

 
0 rows inserted            --<<<<< ----------------------- No errors
 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          Errors are logged in the error log table:

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ COL1 COL2
1 ORA-00001: unique constraint (ALEX.COL1_PK) violated I 1 1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dbmullenCommented:
I'm assuming this is a one-time thing,
disable all the constraints in Oracle prior to loading the data
then enable them when you're done.

begin
for x in (
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name run_me
  from DBA_CONSTRAINTS
 where owner = 'your_schema_owner_here'
 order by table_name)
loop
   execute immediate x.run_me;
end loop;
/


0
 
Mariner73Commented:
If you going to do this regularly, I suggest :
1) create intermidate table or temporally table with exact structure of Msql table without PK
2) Insert all data into this table, there should be no errors (same columns and no PK!)
3) You may add some indexes to accelerate selects.
4) Create some stored procedure wich check, correct and transfer data to your desired table or tables.
5) delete transfered rows from temp table, and you will have into it only error rows
6) decide what to do with errors and drop temp table.
you can make a batch job for Stored procedure.

Otherwise you can use log table as paquicuba suggest.
0
 
neatworkAuthor Commented:
Hi,

I tried the MERGE instead of using INSERT. See query at "code snippet 1". The query returned the following error:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "TABLE3"."MODELID"

Obviously it is not accepted to have the same parameters in the On clause like in the UPDATE statement.

After this error I tried code snippet 2. It returned the following error:
ORA-02064: distributed operation not supported

So is there a way to do this operation using MERGE that's working? Or is using a MERGE just not a good idea in this case?

Any suggestions?

-- code snippet 1
 
MERGE INTO table1 
using (
	select distinct t3.*
	from table1@Prod.world t1, table2@Prod.world t2, table3 t3
	where t1."ModelID" in (No1,No2,No3) 
	and t2."Date" >= yesterday
	and t2."Date" <= actual_date 
	and t2."ModelID" = t1."ModelID") st
ON (t3.ItemID = st."ItemID")
WHEN MATCHED THEN
	UPDATE SET t3.ModelID = st."ModelID";
 
-- code snippet 2
 
MERGE INTO table1 
using (
	select distinct t3.*
	from table1@Prod.world t1, table2@Prod.world t2, table3 t3
	where t1."ModelID" in (No1,No2,No3) 
	and t2."Date" >= yesterday
	and t2."Date" <= actual_date 
	and t2."Model" = t1."Model") st
ON (t3.ItemID = st."ItemID")
WHEN MATCHED THEN
	UPDATE SET t3.ModelID = st."ModelID";

Open in new window

0
 
Mariner73Commented:
This is From Oracle documentation :
" Restrictions on the merge_update_clause
This clause is subject to the following restrictions:

You cannot update a column that is referenced in the ON condition clause.

You cannot specify DEFAULT when updating a view."
here is a link:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

Oracle® Database SQL Reference
10g Release 2 (10.2)

Using merge is a good idea,
about previous code:
-- code snippet 1
ON (t3.ItemID = st."ItemID") -- this is equal on t3.ItemID=t3.ItemID ??!??  st=t3
maybe it should be :
MERGE INTO table1 tt1
...

...
tt1.ItemID=t3.ItemID ?
0
 
dportasCommented:
Your join isn't correct because you haven't referenced Table1 at all. You need to join on your key column(s) - at least that's what you said in the subject line of this question.

0
 
awking00Commented:
See attached.
duplicate-keys.txt
0
 
neatworkAuthor Commented:
@all: Looks like the query was not ok and I messed up the whole query so it might be confusing. Sorry about that!
@Mariner73: Regarding the resultst: st != t3 but st = t1. Even so I run into the two mentioned error messages. Either because UPDATE condition and ON clause are equal or error  
ORA-02064: distributed operation not supported

--code snippet 3
 
MERGE INTO t3
using (
	select distinct t1.*
	from table1@Prod.world t1, table2@Prod.world t2, table3 t3
	where t1."ModelID" in (No1,No2,No3) 
	and t2."Date" >= yesterday
	and t2."Date" <= actual_date 
	and t2."ModelID" = t1."ModelID") st
ON (t3.ModelID = st."ModelID")
WHEN MATCHED THEN
	UPDATE SET t3.ModelID = st."ModelID";

Open in new window

0
 
dportasCommented:
Remove the UPDATE clause.

WHEN MATCHED THEN
      UPDATE SET t3.ModelID = st."ModelID"

You don't need to update on the key value you are joining on (because t3.ModelID = st."ModelID" anyway).
0
 
Mariner73Commented:
OK,
"@Mariner73: Regarding the resultst: st != t3 but st = t1."
you are correct , st is a subset of t1

select distinct t1.*
        from table1@Prod.world t1, table2@Prod.world t2, table3 t3
        where t1."ModelID" in (No1,No2,No3)
        and t2."Date" >= yesterday
        and t2."Date" <= actual_date
        and t2."ModelID" = t1."ModelID") st

1)here you dont need table3, it is not partisipating into resultset or join conditions.

try this:
MERGE INTO t3

using
 
(

        
select
 distinct t1
.*

        
from
 table1@Prod
.
world t1
,
 table2@Prod
.
world t2


        
where
 t1
.
"ModelID"
 
in
 
(
No1
,
No2
,
No3
)
 
        
and
 t2.
"Date"
 
>=
 yesterday
        
and
 t2.
"Date"
 
<=
 actual_date 
        
and
 t2.
"ModelID"
 
=
 t1.
"ModelID"
)
 st
ON 
(
t3
.
ModelID
 
=
 st.
"ModelID"
)

Open in new window

0
 
awking00Commented:
>>Looks like the query was not ok and I messed up the whole query so it might be confusing.>>
You're right.
MERGE INTO table3 t3  ==> you need the tablename here (alias is optional)
using (      select <whatever columns you need for your updates or inserts>
      from <whatever join is required to get the columns for your select>
      where <whatever criteria that needs to be met) st
ON (t3.ModelID = st."ModelID")
WHEN MATCHED THEN
      UPDATE SET t3.ModelID = st."ModelID"; ==> NO! NO! NO!
    You need to update every columns other than ModelID!
      UPDATE SET t3.columns = st.columns
    Plus you need this next section.
WHEN NOT MATCHED THEN
INSERT VALUES(st.ModelID, st.columns)
 
0
 
neatworkAuthor Commented:
Thanks to all of you for the quick help! I solved it by using error logging.
0
 
paquicubaCommented:
Error Logging is a quick and dirty trick to skip dupes and keep on adding...
Glad I could help!
Alex
0
 
awking00Commented:
I agree. It was the first thing I thought of when I saw the question. The second thing I thought of was that paquicuba had already addressed it. :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now