Home » RDBMS Server » Server Utilities » How to update existing table using SQL Loader (oracle 10g)
How to update existing table using SQL Loader [message #525179] Fri, 30 September 2011 00:27 Go to next message
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
Re: How to update existing table using SQL Loader [message #525181 is a reply to message #525179] Fri, 30 September 2011 00:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
Littlefoot
Messages: 21624
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.
Re: How to update existing table using SQL Loader [message #525188 is a reply to message #525185] Fri, 30 September 2011 01:08 Go to previous messageGo to next message
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

Re: How to update existing table using SQL Loader [message #525191 is a reply to message #525188] Fri, 30 September 2011 01:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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
)
Re: How to update existing table using SQL Loader [message #525193 is a reply to message #525191] Fri, 30 September 2011 01:40 Go to previous message
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...
Previous Topic: Tablespace refresh
Next Topic: Issue with data pump export
Goto Forum:
  


Current Time: Thu Dec 03 23:59:17 CST 2020