Home » RDBMS Server » Server Utilities » SQl Loader (Oracle 11g)
SQl Loader [message #608264] Tue, 18 February 2014 04:08 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member

Hi,

Can we do insert/update(Merge) using SQlLoader?

There are more than 1000 records in the file. I need to update/insert into the table using the file.

Please provide me with one example.

Regards,
SRK
Re: SQl Loader [message #608265 is a reply to message #608264] Tue, 18 February 2014 04:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There are examples in the docs, http://docs.oracle.com/cd/E16655_01/server.121/e17639/part_ldr.htm have you been through them yet?
Re: SQl Loader [message #608266 is a reply to message #608264] Tue, 18 February 2014 04:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Switch to external table as it provides flexibility you are looking for. Note that input file (which represents the external table) must reside on a database server (not your own PC, unless it IS the database server).
Re: SQl Loader [message #608268 is a reply to message #608265] Tue, 18 February 2014 04:29 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
The below "Append"statement will only insert into table. Bunt I want to either update/insert( Like Merge ) to the table.

 LOAD DATA 
   INFILE 'ms.txt' 
APPEND   
INTO TABLE Table_name
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ',' 
(
  MSI
) 



Regards,
SRK
Re: SQl Loader [message #608272 is a reply to message #608268] Tue, 18 February 2014 04:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you want to use the SQL MERGE command, then use an external table.
Re: SQl Loader [message #608273 is a reply to message #608272] Tue, 18 February 2014 04:50 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks a lot
Re: SQl Loader [message #608359 is a reply to message #608264] Wed, 19 February 2014 01:21 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SQL*Loader can only insert, not update or merge. You can either use SQL*Loader to load the data into a staging table or create an external table that references the data file. Then you can use the SQL MERGE statement to insert from either the staging table or external table into the target/destination table. External tables can only reference data files on the server. SQL*Loader can load data from files on the client or server.
Previous Topic: EXPDP Error - ORA-39127,ORA-00942,ORA-06512
Next Topic: DataPump Schema
Goto Forum:
  


Current Time: Thu Mar 28 04:30:52 CDT 2024