0

I have CSV file having two columns id_a and id_b, but I need to insert 4 more columns; ie. emp_sal_a, emp_sal_b, emp_dept_a, emp_dept_b using sqlldr. So my current control file looks like:

load data
infile '/home/.../employee.txt'
 into table employee
 fields terminated by ","
 ( id_a, id_b, 
emp_sal_a ":id_a+1000", emp_sal_b "id_b+1000", emp_dept_a "10", emp_dept_b "20")

But I am getting error:

invalid binding variables

halfer
  • 19,824
  • 17
  • 99
  • 186
user2875329
  • 1
  • 2
  • so would it be correct to assume that you're trying to create column value by operating on one of the columns being imported? – ermagana Oct 13 '13 at 04:22
  • Why did you tag this with `mysql`? `sqlldr` is an Oracle tool? –  Oct 13 '13 at 07:11

1 Answers1

0

From MySQL Load Data Ref
note: search for the "(" character and it's the 35th instance of it on the page

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;

@var1 is the name of a variable you want to run an operation on, and what you're doing is calling SET on column2 to be equal to @var1/100.

ermagana
  • 1,090
  • 6
  • 11