Home » RDBMS Server » Server Utilities » Load data into table but delete some reocrds using sql*loader
Load data into table but delete some reocrds using sql*loader [message #355807] Mon, 27 October 2008 12:18 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
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



Thanks,
Re: Load data into table but delete some reocrds using sql*loader [message #355809 is a reply to message #355807] Mon, 27 October 2008 12:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is no native sql*loader method.
Truncate will remove all data.
Replace will remove existing data, but will be very painful for a large load.
Re: Load data into table but delete some reocrds using sql*loader [message #355811 is a reply to message #355809] Mon, 27 October 2008 13:06 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Mahesh.
Appreciate for your help!
Is It REPLACE and TRUNCATE will be the same then?
I need to keep the data for id starts with P1, P2,P3... when i am loading new file daily.
Which option is open for me then?

Thanks,

Re: Load data into table but delete some reocrds using sql*loader [message #355814 is a reply to message #355811] Mon, 27 October 2008 13:21 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
External Tables or a SQL script to delete the rows first.
Re: Load data into table but delete some reocrds using sql*loader [message #355825 is a reply to message #355814] Mon, 27 October 2008 14:43 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Joy.
If i delete the records first then still i need external table or other intermediate table to hold the incoming file data as i need to load those incoming data as well as, right?
Is is SQL*LOADER has any where clause kind of so i can avoid those data while its replace or truncate?

Thanks,
Re: Load data into table but delete some reocrds using sql*loader [message #355826 is a reply to message #355825] Mon, 27 October 2008 15:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I am not quite sure I understand your statement about external tables.

If you need to delete or keep records in your table before SQL*Loader runs, then run a script to do so.

There is no WHERE clause for SQL*Loader to do this for you.


Re: Load data into table but delete some reocrds using sql*loader [message #355832 is a reply to message #355826] Mon, 27 October 2008 17:01 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
So i have to follow steps:
1)create the external table then load data into ext table
2)delete the data from original table using where id not like 'P%'
3) then run the Update/Insert script to load the new data into original table using external table

This is a daily job.

Please let me know if i am missing anything.

thanks,
Re: Load data into table but delete some reocrds using sql*loader [message #361979 is a reply to message #355832] Sat, 29 November 2008 00:57 Go to previous messageGo to next message
vboppa
Messages: 22
Registered: November 2008
Junior Member
You can selectively load the data by using "WHEN" and "CONTINUEIF" clauses.
Re: Load data into table but delete some reocrds using sql*loader [message #361981 is a reply to message #361979] Sat, 29 November 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How? Post a working control file with the test case OP posted.

Regards
Michel
Re: Load data into table but delete some reocrds using sql*loader [message #363227 is a reply to message #361981] Fri, 05 December 2008 07:37 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Following is my current working control file.
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)
)



And follwoing is the sample data look like from the file which i am loading everyday and also i am running insert script to load some data which is not in the file.
Now i need to keep this INSERT sql data as it is while i am loading through sql*loader but i am using Truncate option so its truncating whole table and i have to run each time this INSERT sql after sql*loader runs.If i add any Insert sql which is not part of the INSERT script and not into file then its also deleting when Sql*loader runs then i have to manually add insert sql in my original INSERT script which i am running after sql*loader runs but sometimes chances to forget to add into INSERT script and its get deleted then you don't know so i am looking some approach to handle this situation.

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



Thanks,
Use partitions [message #379088 is a reply to message #355807] Sun, 04 January 2009 12:43 Go to previous messageGo to next message
ignasi
Messages: 3
Registered: November 2008
Junior Member

Hi there,

Keep the permanent data in one partition and temporary
on another partition.

...
into table <name> (partition <name>)
...

Regards
Re: Use partitions [message #379683 is a reply to message #379088] Wed, 07 January 2009 11:32 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi ignasi,
Thanks for your tips but we don't use any partitioning.
If you have other idea?

Regards,
Poratips
Previous Topic: Sqlldr Date Issues
Next Topic: how to specify log files for exporting and importing logical objects
Goto Forum:
  


Current Time: Mon Dec 05 11:12:48 CST 2016

Total time taken to generate the page: 0.10034 seconds