Home » RDBMS Server » Server Utilities » update through sql loader
update through sql loader [message #255234] Tue, 31 July 2007 02:17 Go to next message
annu-agi
Messages: 203
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
Re: update through sql loader [message #255237 is a reply to message #255234] Tue, 31 July 2007 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
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

Re: update through sql loader [message #255287 is a reply to message #255234] Tue, 31 July 2007 05:07 Go to previous messageGo to next message
annu-agi
Messages: 203
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"
)


Re: update through sql loader [message #255290 is a reply to message #255287] Tue, 31 July 2007 05:10 Go to previous message
Michel Cadot
Messages: 64130
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:
  


Current Time: Wed Dec 07 10:28:19 CST 2016

Total time taken to generate the page: 0.14229 seconds