OraFAQ Forum: Server Utilities » How to update existing table using SQL Loader

Home » RDBMS Server » Server Utilities » How to update existing table using SQL Loader (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to update existing table using SQL Loader [message #525179]

Fri, 30 September 2011 00:27

shiveclat
Messages: 19
Registered: August 2011
Location: pune

Junior Member

Hi All,
I am using a shell script to load unix content to a database.
I have captured the unix data to a csv file and I am using a sql loader to inser that csv data to database. following is my ctl file contents.

[b]load data
infile data.csv
into table AVS_LOGS
fields terminated by ',' 
(
RUNDATETIME,
SERVER,
DIRECTORY,
FILENAME,
LASTUPDATETIMESTAMP
) [/b]

and I am using sql loader command in unix that is

[b]sqlldr $CLOGIN control=control.ctl log=test.log[/b]

But this is working only if the table is empty.
Now I am looking for something where I do not need to delete the data from table each time. it should update the table.
can some please help me into this.
Thanks

Report message to a moderator

Re: How to update existing table using SQL Loader [message #525181 is a reply to message #525179]

Fri, 30 September 2011 00:40

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

If you mean append, then:

load data
APPEND
infile data.csv
into table AVS_LOGS
fields terminated by ','
(
RUNDATETIME,
SERVER,
DIRECTORY,
FILENAME,
LASTUPDATETIMESTAMP
)

If you actually mean update, then load into a staging table and use merge.

[Updated on: Fri, 30 September 2011 00:40]

Report message to a moderator

Re: How to update existing table using SQL Loader [message #525185 is a reply to message #525181]

Fri, 30 September 2011 00:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Or, use external tables feature which makes it possible to access your CSV file as if it was an Oracle table. You could select from it, join it with your "real" table, write (almost) any query or a procedure that uses the external table. Check the documentation for more information.

Report message to a moderator

Re: How to update existing table using SQL Loader [message #525188 is a reply to message #525185]

Fri, 30 September 2011 01:08

shiveclat
Messages: 19
Registered: August 2011
Location: pune

Junior Member

Hi Barbara Boehmer
I tried with the append in the control file as suggested above and I got Error like

sqlldr impactmgr/alton02@BUSDEV control=control.ctl log=test.log
SQL*Loader: Release 11.1.0.7.0 - Production on Fri Sep 30 02:06:55 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
SQL*Loader-350: Syntax error at line 4.
Expecting keyword INTO, found keyword infile.
infile data.csv

Report message to a moderator

Re: How to update existing table using SQL Loader [message #525191 is a reply to message #525188]

Fri, 30 September 2011 01:16

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Sorry, I accidentally put it in the wrong place.

load data
infile data.csv
APPEND
into table AVS_LOGS
fields terminated by ','
(
RUNDATETIME,
SERVER,
DIRECTORY,
FILENAME,
LASTUPDATETIMESTAMP
)

Report message to a moderator

Re: How to update existing table using SQL Loader [message #525193 is a reply to message #525191]

Fri, 30 September 2011 01:40

shiveclat
Messages: 19
Registered: August 2011
Location: pune

Junior Member

Wonderfull.... Thank you So much Barbara.
Now I can Enjoy my Weekend... Have a nice weekend to you...

Report message to a moderator

Previous Topic:	Tablespace refresh
Next Topic:	Issue with data pump export

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 21 18:01:05 CST 2025