I have script like this
0HURRY10001
10000003301XX04042019080100000085000000000000
10000004325XY31712019072900000090000000000000
90002
Where I need to enter this data position wise into my table for eg:
number : 0000003301
type : XX
register : 0404
date : 20190801
balance : 0000008500
last_transaction : 0000000000
promotion_available : HURRY10001
Above are the columns in my sql table all fields are not null
I am writing SQL loader script for the first time how will I be able to do so for such data ?
You can use positional notation to state the location of each field.
I'm assuming that the first character in each record states its type. And you want to load these into different tables.
In which case here's an example to get you started:
LOAD DATA
infile *
APPEND
INTO TABLE t1 WHEN rec_type = '1'
(
rec_type filler position(1:1) CHAR ,
num position(2:11) CHAR ,
type position(12:13) CHAR,
<etc.>
)
INTO TABLE t2 WHEN rec_type = '0'
(
rec_type filler position(1:1) CHAR ,
promo position(2:11) CHAR
)
begindata
0HURRY10001
10000003301XX04042019080100000085000000000000
10000004325XY31712019072900000090000000000000
90002