OraFAQ Forum: Server Utilities » update through sql loader

Home » RDBMS Server » Server Utilities » update through sql loader

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

update through sql loader [message #255234]

Tue, 31 July 2007 02:17

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

hi experts

i have a table named test_imp and structure like as given below

Name Null? Type
----------------------------------------- -------- ----------------------------
ACC_CD NOT NULL VARCHAR2(10)
TRANS_DATE VARCHAR2(10)
DECRIPT VARCHAR2(100)
AMT NUMBER(14,2)

it has 10 records at that moment ...
the same structure i designed on excell and save it as a comma delimated form (CSV)

and i use sqlldr to extract data from CSV file and store in Table .. it perfectly works.. now i would like to update this file with more 10 records .. and sqlldr gives me an error like

SQL*Loader-601: For INSERT option, table must be empty. Error on table TEST_IMP

it means everytime when i need to transfer data i must delete all the data first and then rewrite again ... is there any way that i just update my data with specific records those which is not in table ..any idea with sql loader ... or any other way .. what is a best practice ... Here i would like to mension ACC_CD is a PK..

regards

Anwer

Report message to a moderator

Re: update through sql loader [message #255237 is a reply to message #255234]

Tue, 31 July 2007 02:26

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Utilities
Part II, SQL*Loader
Chapter 8, SQL*Loader Control File Reference
Section "Loading Logical Records into Tables"
Subsection "Table-Specific Loading Method"

Regards
Michel

Report message to a moderator

Re: update through sql loader [message #255287 is a reply to message #255234]

Tue, 31 July 2007 05:07

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

hi experts

see the script and tell me where i m making a mistake to enter constant data for com_Cd and loc_cd . it causeing an error

Record 1: Rejected - Error on table DEALER_INFO, column COM_CD.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table DEALER_INFO, column COM_CD.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table DEALER_INFO, column COM_CD.
Column not found before end of logical record (use TRAILING NULLCOLS)

load data
infile 'M:\dbf_only\ighdeler.TXT'
into table dealer_info
fields terminated by ","
optionally enclosed by '"'

(
CAT_ID ,
D_CODE ,
MEM_DT date 'yyyymmdd',
NAME ,
PERSON ,
ADDRESS1 ,
ADDRESS2 ,
ADDRESS3 ,
PHONE ,
CLASS ,
CR_APP ,
CR_TERMS ,
ACCOUNT ,
CR_LIMIT ,
OPENING ,
TARGET ,
TOT_LIAB ,
VISIT1 ,
VISIT2 ,
VISIT3 ,
VISIT4 ,
VISIT5 ,
VISIT6 ,
VISIT7 ,
TOT_AMT ,
SHOP_ON ,
SHOP_OFF ,
SHOP_POS ,
CLOSE_D date 'yyyymmdd',
START_D date 'yyyymmdd',
FAX ,
D_K_NO ,
DEEP_FREZ ,
CASH_FREE ,
NEW_FLAG ,
GST_NO ,
NTN_NO ,
com_Cd char "01",
dealer_cd char "LPAD(ROWNUM, 10, '0')",
loc_Cd char "01"
)

Report message to a moderator

Re: update through sql loader [message #255290 is a reply to message #255287]

Tue, 31 July 2007 05:10

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read and follow How to format your posts

I think the message is clear

Quote:

Column not found before end of logical record (use TRAILING NULLCOLS)

Now you just have to open the documentation.

Regards
Michel

[Updated on: Tue, 31 July 2007 05:10]

Report message to a moderator

Previous Topic:	exp/imp only data
Next Topic:	import

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:33:09 CDT 2024