How to avoid few data when using sql*loader with Truncate
633450Oct 27 2008 — edited Oct 27 2008Hi,
I am using sql*loader to load data everyday for some incoming file using Truncate option (reloading table) and also running.
Now when i trucate this table to load incoming file then my seperate loaded sql data (from my insert after sql*loader) also get truncated and i have to run again insert sql everyday.
My seperate sql'a pk id column starts with special char so i know that these are from my seperate isnert sqls.
Now how can i use the sql*loader so when i truncate/delete the table then i keep the seperate sql'a pk id column data which starts with special char and load only incoming file?
My table structure and sql*loader is:
CREATE TABLE CODE
(
ID VARCHAR2(40 BYTE) NOT NULL,
CODE_NAME VARCHAR2(5 BYTE) NOT NULL,
CODE_VALUE VARCHAR2(17 BYTE),
CTL VARCHAR2(6 BYTE),
DESC VARCHAR2(20 BYTE) NOT NULL,
JOB VARCHAR2(1 BYTE),
TERM VARCHAR2(1 BYTE),
CONSTRAINT CODE_PK PRIMARY KEY (ID)
SQL*LOADER:
---------
load data
infile DATA2.txt'
truncate
into table CODE
(id recnum,
code_name position(1:5),
code_value position(6:22),
ctl position(23:28),
desc position(29:48),
JOB position(49:49),
TERM position(50:50)
)
example Data:
1 EC01 B1 Beta J T
2 EC01 B2 Beta J T
3 EC01 B3 Beta J T
4 EC01 C1 test J T
5 EC01 C2 test J T
6 EC01 C3 test J T
7 EC01 C4 test J T
8 EC01 C5 test J T
9 EC01 C6 test J T
P1 EC01 PR1 MAX J T
P2 EC01 PR2 MAX J T
P3 EC03 PR3 MAX J T
==> P1, P2, P3... data which is i am loading after sql*loader truncate the table and load new file.
I want to keep this P1, P2, P3 data when sql*loader truncate the table and reload new file each time.
Thanks,