| sql loader - how to ignore inserting duplicate records [message #450742] |
Fri, 09 April 2010 01:14  |
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 #450845 is a reply to message #450742] |
Fri, 09 April 2010 10:03  |
 |
Barbara Boehmer
Messages: 9102
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
