sqlldr, add file date to each record

[Beantree]

I have a SQLLDR controlfile which is working. In the data file, there is a date in the header record (1'st record) which I normally skip. I want to put this date in a column of each record which loads. I can't find anything in the SQLLDR controlfile reference which will allow me to do this.

Any ideas?

Thanks

 

[THoey]

The only way that I can think to do this is to load the date into a temp table, load the rest of the data into your data table and then do an update on the data table to add the date. If the first row of the data file (header record) consistantly has some sort of title, you could do the SOMETHING LIKE the following for your INTO statement of your control file:

INTO TABLE MyTemp
WHEN (01:12) = 'Report Title'
(
ReportDate (13:33) DATE
)
INTO TABLE MyData
WHEN (01:12) <> 'Report Title'
(
...
)

For the MyData section, I would set the date field to a constant, and then run an update query that would change this to the date stored in the MyTemp table.

I BOLDED SOMETHING LIKE in the above paragraph because I have never done this and am just getting ideas from one of the books I have. It's example isn't like your idea, but I can see similarities.

Hope it helps.... Terry M. Hoey

 

[srishan]

Try using Header column value in all your lines.
I am using Header and Line wording to differentiate between your first record(header) and remaining records(lines)
Example: line_col1 &quot;:Header_col&quot;
This will put the value of Header column value in any column you want.

 

[srishan]

Try using Header column value in all your lines.
I am using Header and Line wording to differentiate between your first record(header) and remaining records(lines)
Example: line_col1 &quot;:Header_col&quot;
This will put the value of Header column value in any column you want.
-----------------
To add to this,
You need a column in the table to take Header_col value from the first record. After that &quot;:Header_col&quot; notation works like a constant. I do not think that I explained it clear encough the first time.
Sri