Home » RDBMS Server » Server Utilities » update using sqlldr
update using sqlldr [message #352611] Wed, 08 October 2008 10:34 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
CREATE TABLE EMP
(
  ENO    NUMBER(4),
  ENAME  VARCHAR2(4 BYTE)
)



LOAD DATA
    INFILE 'sample.dat'
    fields terminated by ','
   BADFILE 'sample.bad'   DISCARDFILE 'sample.dsc'
    APPEND
    INTO TABLE emp
    (
      ENO,
      ENAME)   
    


Now how do I update the rows..the above example inserts it.

I have referred documentation, and it says replace option
doesnt work..it says update with correlated subqueries

can anyone help me out?
Re: update using sqlldr [message #352612 is a reply to message #352611] Wed, 08 October 2008 10:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Now how do I update the rows..the above example inserts it.
You cannot. Sqlldr will not update.
Use a staging table to load data as-is.
Use merge statements to update.
Re: update using sqlldr [message #352616 is a reply to message #352611] Wed, 08 October 2008 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "external table" and MERGE.

Regards
Michel
Re: update using sqlldr [message #352784 is a reply to message #352611] Thu, 09 October 2008 10:12 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
OK I am doing this, the user gives me the columns and data to be updated in excel, I am loading
that into a staging table..and then do merge.

Data and table is something like this


create table ldrtest(code varchar2(10),id varchar2(10),
                     row_name varchar2(25),COL1 NUMBER(4),
COL2 NUMBER(4),COL3 NUMBER(4),crt_tm date, md_tm date, 
crt_user varchar2(10),MOD_USR VARCHAR2(10)) 
/

CREATE UNIQUE INDEX ldrtest_UK3 ON ldrtest
(CODE, ID)


INSERT INTO LDRTEST VALUES('24','0','AGREEMENT',1,1,2,SYSDATE,TO_DATE('2008/08/17','YYYY/MM/DD'),'SCOTT','PETER')
/

INSERT INTO LDRTEST VALUES('24','0','FINAGREE',1,1,2,SYSDATE,TO_DATE('2008/09/10','YYYY/MM/DD'),'SCOTT','JOE')

/

INSERT INTO LDRTEST VALUES('74','704','AGREEMENT',1,1,2,SYSDATE,NULL,'SCOTT',NULL)

/



INSERT INTO LDRTEST VALUES('74','704','FINAGREE',1,1,2,SYSDATE,NULL,'SCOTT',NULL)
/




1. There is a unique index on id and code, I am not aware why duplicate rows are there.

2. If you observe the data, its evident that though there is a unique index on id and code columns,
I should also consider row_name if I have to guarentee uniqueness..note that modified time
and modified user values are changed. So, in my merge condition, should I include
3 conditions on my on clause?

something like on(a.code = b.code and a.id = b.id and a.row_name = b.row_name)..?

3. The user gives me an excel with some columns which have to be updated, and their values,
should I ask them to provide all the columns in the excel..so that I do not have to
change my merge each and every time?

that excel would be put in a scheduler..we would delete the file once the job is done

[Updated on: Thu, 09 October 2008 10:19] by Moderator

Report message to a moderator

Re: update using sqlldr [message #352799 is a reply to message #352611] Thu, 09 October 2008 11:31 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
alright, the merge on multiple condition works


MERGE INTO ldrtest D
   USING (SELECT inv_code,pool_id,row_name,col1,col2,col3,crt_tm,md_tm,crt_user,mod_usr FROM stg_ldrtest) S
      ON (d.inv_code = s.inv_code and d.pool_id = s.pool_id )
   WHEN MATCHED THEN UPDATE SET  d.row_name = s.row_name,
                                d.col1 = s.col1,
                                d.col2 = s.col2,
                                d.col3 = s.col3,
                                 d.crt_tm = s.crt_tm,
                                d.md_tm = s.md_tm,
                                d.crt_user = s.crt_user,
                                d.mod_usr = s.mod_usr
        WHEN NOT MATCHED THEN INSERT (d.inv_code,d.pool_id,d.row_name,d.col1,d.col2,d.col3,d.crt_tm,d.md_tm,d.crt_user,d.mod_usr)
     VALUES (s.inv_code,s.pool_id,S.row_name, s.col1,s.col2,s.col3,s.crt_tm,s.md_tm,s.crt_user,s.mod_usr);
     





maybe something wrong with the data..because using merge
will fail with the above data
Re: update using sqlldr [message #352804 is a reply to message #352611] Thu, 09 October 2008 12:17 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
but if the user agrees to give me only those columns which
have to be updated..then i have to think about an alternative
Re: update using sqlldr [message #352814 is a reply to message #352611] Thu, 09 October 2008 13:56 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
the table has a unique index on id, code and row_name
so no problem.i am wondering how to take care of things
if the user supplies me only the updated columns and the
above 3 columns with values
Re: update using sqlldr [message #353036 is a reply to message #352784] Fri, 10 October 2008 12:43 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ora1980 wrote on Thu, 09 October 2008 11:12


create table ldrtest(code varchar2(10),id varchar2(10),
                     row_name varchar2(25),COL1 NUMBER(4),
COL2 NUMBER(4),COL3 NUMBER(4),crt_tm date, md_tm date, 
crt_user varchar2(10),MOD_USR VARCHAR2(10)) 
/

CREATE UNIQUE INDEX ldrtest_UK3 ON ldrtest
(CODE, ID)


INSERT INTO LDRTEST VALUES('24','0','AGREEMENT',1,1,2,SYSDATE,TO_DATE('2008/08/17','YYYY/MM/DD'),'SCOTT','PETER')
/

INSERT INTO LDRTEST VALUES('24','0','FINAGREE',1,1,2,SYSDATE,TO_DATE('2008/09/10','YYYY/MM/DD'),'SCOTT','JOE')

/



1. There is a unique index on id and code, I am not aware why duplicate rows are there.



You are having a pretty good conversation with yourself Wink
There is no unique index as you never terminated your line with a semicolon or slash.
Previous Topic: SQL*Loader logging off?
Next Topic: Using TRIM in sql loader control file
Goto Forum:
  


Current Time: Fri Dec 09 01:57:13 CST 2016

Total time taken to generate the page: 0.05436 seconds