Home » RDBMS Server » Server Utilities » sql loader - how to ignore inserting duplicate records (Oracle 10g)
sql loader - how to ignore inserting duplicate records [message #450742] Fri, 09 April 2010 01:14 Go to next message
anijan
Messages: 5
Registered: April 2010
Junior Member
Hi,

Im calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.

Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.

Please let me if there is are any other way to ignore inserting duplicate records into the tables.

Thanks in advance.

[Updated on: Fri, 09 April 2010 01:26] by Moderator

Report message to a moderator

Re: sql loader - how to ignore inserting duplicate records [message #450746 is a reply to message #450742] Fri, 09 April 2010 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 options:
1/ Disable the unique constraint and handle the duplicate in SQL before reenabling the constraint
2/ Remove the duplicates from the file before loading.

Regards
Michel
Re: sql loader - how to ignore inserting duplicate records [message #450805 is a reply to message #450746] Fri, 09 April 2010 05:51 Go to previous messageGo to next message
anijan
Messages: 5
Registered: April 2010
Junior Member
can u pls give more deatils on option1.

option2 is not possible as we are not supposed to do that.
Re: sql loader - how to ignore inserting duplicate records [message #450806 is a reply to message #450742] Fri, 09 April 2010 06:01 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
What details do you want exactly?
You disable the constraint, load the data, delete the duplicates and re-enable the constraint.
Re: sql loader - how to ignore inserting duplicate records [message #450809 is a reply to message #450805] Fri, 09 April 2010 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, option 1 presupposes you forbid any access from other sessions to the table you load if you don't want they receive false results.

Regards
Michel
Re: sql loader - how to ignore inserting duplicate records [message #450829 is a reply to message #450742] Fri, 09 April 2010 07:59 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Would using
ROWS=1
help in your case? I believe the rows violating the constraint will just error out, but the load will continue.
Re: sql loader - how to ignore inserting duplicate records [message #450845 is a reply to message #450742] Fri, 09 April 2010 10:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You could create a view on the table, then create an instead-of trigger on the view that filters out the duplicates, then have sqlldr load into the view. The result would be that each first unique value would be loaded, each subsequent duplicate value would not be loaded, and no errors would be raised. Please see the demo below. In the demo below, I just ignored the duplicates, but you could insert them into a duplicate table of some sort instead if you like.

-- test1.csv:
1,name1,
2,name2a,
2,name2b,
3,name3a,


-- test2.csv:
2,name2c,
2,name2d,
3,name3b,
4,name4,


-- table with primary key:
SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (id	    NUMBER,
  3  	name	    VARCHAR2 (10),
  4  	CONSTRAINT  id_pk PRIMARY KEY (id))
  5  /

Table created.


-- view and trigger:
SCOTT@orcl_11g> CREATE OR REPLACE VIEW test_view
  2  AS SELECT * FROM test_tab
  3  /

View created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER test_trig
  2    INSTEAD OF INSERT ON test_view
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO test_tab VALUES
  6  	 (:NEW.id, :NEW.name);
  7  EXCEPTION
  8    WHEN DUP_VAL_ON_INDEX THEN NULL;
  9  END test_trig;
 10  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- test.ctl:
OPTIONS(ERRORS=0)
LOAD DATA
INFILE 'test1.csv'
INFILE 'test2.csv'
INTO TABLE test_view
FIELDS TERMINATED BY ','
(id, name)


-- load and reults without duplicates:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  /

        ID NAME
---------- ----------
         1 name1
         2 name2a
         3 name3a
         4 name4

SCOTT@orcl_11g>


-- log without errors:

SQL*Loader: Release 11.1.0.6.0 - Production on Fri Apr 9 07:50:18 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Control File:   test.ctl

There are 2 data files:
Data File:      test1.csv
  Bad File:     test1.bad
  Discard File:  none specified
 
 (Allow all discards)
Data File:      test2.csv
  Bad File:     test2.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 0
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST_VIEW, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,       CHARACTER            
NAME                                 NEXT     *   ,       CHARACTER            


Table TEST_VIEW:
  8 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             8
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Apr 09 07:50:18 2010
Run ended on Fri Apr 09 07:50:19 2010

Elapsed time was:     00:00:01.31
CPU time was:         00:00:00.07


[Updated on: Fri, 09 April 2010 11:20]

Report message to a moderator

Re: sql loader - how to ignore inserting duplicate records [message #450847 is a reply to message #450845] Fri, 09 April 2010 10:28 Go to previous message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice solution; I'm a little bit afraid about the performances.
And I think OP will come back saying it is not allowed to create objects in the database. Wink

Regards
Michel

[Updated on: Fri, 09 April 2010 10:29]

Report message to a moderator

Previous Topic: sql loader
Next Topic: import hangs
Goto Forum:
  


Current Time: Mon Jan 25 02:15:53 CST 2021