CODEWITHSUNDEEP
oracle-databasesql-loader
Buddy
Buddy

Reputation: 43

SQL LOAD DATA INFILE but keep one field static

I am running the following to load data into a table

OPTIONS (Skip=1)
LOAD DATA
INFILE 'D:\EPM\import\test.txt' 
APPEND
INTO TABLE HYP.HS_MEMBER_D
FIELDS TERMINATED BY "|" 
TRAILING NULLCOLS
(
DIMENSION,
PARENT,
CHILD,
ALIAS,
ATTRB01
)

This is working fine but I want to keep the ATTRB01 field as a static value, I want to load "Alloc" for all records, is there a way in this script to load a static value even though I am loading from the file for the other fields?

Upvotes: 1

Views: 1269

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

You'd load a constant, such as

OPTIONS (Skip=1)
LOAD DATA
INFILE 'D:\EPM\import\test.txt' 
APPEND
INTO TABLE HYP.HS_MEMBER_D
FIELDS TERMINATED BY "|" 
TRAILING NULLCOLS
(
DIMENSION constant Account,
PARENT,
CHILD,
ALIAS,
ATTRB01  constant 'Alloc'       --> this
)

Here's an example: test table:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ID                                                 NUMBER
 ATTRB01                                            VARCHAR2(20)

Control file:

load data 
infile *
replace
into table test
fields terminated by ","
trailing nullcols
(
id,
attrb01 constant 'Alloc'
)

begindata
1,xxx
2,yyy
3,zzz

Loading session & the result:

SQL> $sqlldr scott/tiger control=test01.ctl log=test01.log

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Kol 15 21:08:59 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * From test;

        ID ATTRB01
---------- --------------------
         1 Alloc
         2 Alloc
         3 Alloc

Upvotes: 1

kanagaraj
kanagaraj

Reputation: 442

try by using set at end

OPTIONS (Skip=1)
LOAD DATA
INFILE 'D:\EPM\import\test.txt' 
APPEND
INTO TABLE HYP.HS_MEMBER_D
FIELDS TERMINATED BY "|" 
TRAILING NULLCOLS
(
DIMENSION constant Account,
PARENT,
CHILD,
ALIAS,
ATTRB01
)
set ATTRB01='default_value'

Upvotes: 0

Related Questions