CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloperplsqldeveloper
user1547554
user1547554

Reputation: 591

Setting default column values for Sql Developer Import

I've the following Log table in my database:

FROM_PARTY_ID   VARCHAR2(15 CHAR)   Not Null
TO_PARTY_ID VARCHAR2(15 CHAR)   Not Null
CREATED_DT  DATE    Not Null
UPDATED_DT  DATE    Null
NO_OF_TRIES NUMBER(3,0) Null
APPLICATION_ID  VARCHAR2(15 CHAR)   Not Null

Now, I want to import the data from one .CSV file to this log table, the structure of the CSV file is:

Old_PartyID,New_PartyID,CREATED_DT,UPDATED_DT,NO_OF_TRIES,APPLICATION_ID
015110044200015,099724838000015
069167641100015,099724838000015
016093943300015,099728485000015
033264160300015,099728485000015
035968914300015,099728485000015
087580324300015,099728485000015

I don't have any data for the Created Date and Application ID column in this file, however, I need to insert Sysdate as default Created_DT and XYZ as default Application_ID.

Now when I try to import this data in to the log table using the Import facility present in the Sql Developer (By right clicking on table name), it doesn't allow me to set any default values for created date and application id, since these options are disabled and the insertion fails because these columns are not null.

Can someone guide me that how do I insert these values in the table, specially that Created date as sysdate.

(I've to stick with this import option only, I'm not allowed to go for any other approach). Thanks in advance.

Upvotes: 4

Views: 2715

Answers (1)

Kacper
Kacper

Reputation: 4818

Can you define table as:

create table tabbb (
FROM_PARTY_ID   VARCHAR2(15 CHAR)   Not Null,
TO_PARTY_ID VARCHAR2(15 CHAR)   Not Null,
CREATED_DT  DATE default sysdate,
UPDATED_DT  DATE    Null,
NO_OF_TRIES NUMBER(3,0) Null,
APPLICATION_ID  VARCHAR2(15 CHAR) default 'XYZ' );

In case you don't have access to alter table please use import via sqlloader (when import from sql developer as import method choose SQL*Loader utility) and define default in control file. default value in control file Load with SQLoader via SQLDeveloper

Or option 2. Define external table to your csv file. Select from external table replacing nulls with some values and insert.

Upvotes: 2

Related Questions