SQL Loader

In the control file for SQL Loader is there a way to specify in the when clause to load a specific row of data into a specific table?  For example, I want my 2nd row of data to load into TABLE1 and all other rows to load into table 2.

RowHeadingsfor Table1
Row2ofdatacolumns
RowHeadingsfor Table2

Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2|Col2|Tab2|Col3|TAb2|Col4
newtoperlpgmAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Since you never posted back I'm not sure if you can delimit the rows in some way.

You can run sql loader twice.
First skip one row then only load one:
SKIP=1 LOAD=1

Then just SKIP=2 to load the rest
0
 
slightwv (䄆 Netminder) Commented:
You can do this if there is some way to tell the difference in the rows other than line number.  At least I'm not sure of a way based on file position.

Is there something in the data that gives a clue what goes where?
0
 
OP_ZaharinCommented:
- based on your file format, it is quite difficult to load the data. if you have an indicator/'row marker' to differentiate the data on the 2nd row and the 3rd row and the following row (as illustrate below) then you can use WHEN function to differentiate the rows. i use '1' and '2' in the first column as a 'row marker':

Col1|Col2|Col3|Col4|Col5
1|Tab1Col1|Tab1Col2|Tab1Col3|Tab1Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4

- the following code use WHEN to differentiate the row for table1 and table2. (01) is the first position of the record. then use POSITION and FILLER to get to the beginning of the record and skip the first column which is the 'row marker'.
 
LOAD DATA
INFILE   'datafile.dat'
APPEND
 INTO TABLE table1
 WHEN (01) = '1'
FIELDS TERMINATED BY "|"
( rec_skip filler POSITION(1), column1 , column2 )
INTO TABLE table2
  WHEN (01) = '2'
FIELDS TERMINATED BY "|"
(rec_skip filler POSITION(1), column1, column2 )

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
newtoperlpgmAuthor Commented:
The data files formats are making this quite difficult, that is why I posted the question, to see if there was an easy way I may be missing to skip the column header rows.  I use WHEN statements, but they don't seem to be working correctly, unfortunately.  I am going to try the proposed solutions here and I will post my results, hopefully success.  Thanks.
0
 
slightwv (䄆 Netminder) Commented:
>>easy way I may be missing to skip the column header rows

I thought I posted that in http:#a35512688?
0
 
OP_ZaharinCommented:
- actually by using WHEN together with the 'row marker', you can skip the header row by not specifying a 'row marker' for that row OR specify any marker different from the 'row marker' for data.
0
 
newtoperlpgmAuthor Commented:
Is there any way to skip line 1 and line 3 in one options statement?
SKIP=1 LOAD=1

Then just SKIP=2 to load the rest
0
 
OP_ZaharinCommented:
in posting ID35512872, if you did not set the marker for line 1 and 3, then using the WHEN it will not take the data. the marker can be any number/char that is unique:

Col1|Col2|Col3|Col4|Col5
1|Tab1Col1|Tab1Col2|Tab1Col3|Tab1Col4
Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4

0
 
slightwv (䄆 Netminder) Commented:
I've never seen a way to specify more than one OPTIONS clause in a single controlfile.

I'm not sure what you mean by skip line 1 and 3.
0
 
newtoperlpgmAuthor Commented:
OP_Zaharin:  can I set my row marker in my control file?  My data files won't have row numbers/markers.
Thanks.
0
 
slightwv (䄆 Netminder) Commented:
I talked about this in the first two posts:  If you cannot 'flag/tag/identify' the rows in some way other than row position, you cannot use WHEN.

I guess I should say:  I don't know how to use WHEN for row position.
0
 
OP_ZaharinCommented:
- no u can't, u specify which marker to read and skip in your control file. you set the marker, in you data file.

- i would suggest you to write a program to load the data, not using sqlloader. i used to write a vb program (long time ago) to load data into my database.
0
 
slightwv (䄆 Netminder) Commented:
Write/debug a program to keep from calling sql loader twice?

I probably wouldn't.
0
 
OP_ZaharinCommented:
- write a program to read the data file and load the data to the database. no sqlloader involve.
0
 
OP_ZaharinCommented:
newtoperlpgm,
- if you don't mind, can you share with us 4-5 lines of your DAT file? we need to understand further the format of your file.

- looking at your example data format, can i say that you want to:
i- skip line 1
ii- insert line 2 data into table1
iii- skip line 3
iv- insert line 4 and the rest into table2
 
Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2|Col2|Tab2|Col3|TAb2|Col4

- if the above is true, can i know if the 1st column of the 2nd row data is unique? assuming that if it is unique, i'm preparing the following example to illustrate my code. this is the close that i can get. there might be other way of doing this but i haven't found any yet:

-- the DAT file
-- assuming the column1 in line2 data (=1) is unique from the rest of the line:

Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2Col2|Tab2Col3|TAb2Col4
2|3|4|5|
12|13|14|15|
22|23|24|25|

Open in new window


-- the CTL file
-- SKIP 1 : will skip the 1st line header
-- WHEN col1 = '1' : this condition check unique data in column1 for line 2 and insert to table1
-- WHEN col1 <> 'Tab2Col1' AND col1 <> '1' : this condition check the rest if the data and skip the 2nd header ('Tab2Col1') and 2nd line data in col1 that is unique from other data '1'

OPTIONS (SKIP=1)
LOAD DATA 
INSERT INTO TABLE table1
WHEN col1 = '1'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
(  
col1,
col2,
col3,
col4,
col5
)
INTO TABLE table2
WHEN col1 <> 'Tab2Col1' AND col1 <> '1'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
(  
col1 POSITION(1),
col2,
col3,
col4
)

Open in new window


- the data inserted to the table will be as follows:

Table1
======
1 | 2 | 3 | 4 | 5

Table2
======
2 | 3 | 4 | 5
12 | 13 | 14 | 15
22 | 23 | 24 | 25
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.