asked on

How do I convert a physical row into multiple rows using SQL Loader

Hello,

      My client is going to provide me with an Excel spreadsheet that will be saved as a .CSV file. I am planning to us SQLLoader to load the data into a table. Here is my situation, the data has a date range, but I would like to load one row for each date in the date range.

Example:
The spreadsheet will look like this
State      From date      To date
01      01-JAN-2010      055-JAN-2010

I would like to load,

State      Date
01      01-JAN-2010
01      02-JAN-2010
01      03-JAN-2010
01      04-JAN-2010
01      05-JAN-2010

I know that I can load the data as it is, and then create the table I need, but I would like to know if I can bypass using an interment table.

(This is the select that I would use to create my final table if I can not have SQLLoader create the table.)

select state, period_begin_date
from AA,
global.times_dimension
where period_begin_date between from_date and to_date
and hierarchy_code = '--'

*tines_dimension table has date ranges for daily weekly monthly, quarterly and yearly in multiple formats.

Has anyone done something like this? Any suggestions?

ASKER CERTIFIED SOLUTION

ishando

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike_Brinton

ASKER

Thanks. I was hoping that it could be done in the SQL Loader step. I was planning to place place a trigger in table A (where the data will be loaded) to populate table B, but changes in the data are not forcing me to execute a seperate insert statement for table B. Thanks again for your help.