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


      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.

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,
  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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't think SQLLoader can handle this type of conversion directly.
What I would look at, is loading the data into a staging table as is, then using either a trigger, procedure or insert/select to convert the ranges into individual  records, as you are doing it in the select statement.

Trigger has the advantage that you would only have to run SQL Loader, then it would take care of ensuring the data got loaded into the final table. The disadvantage with it, is that it would probably slow down your load - though affect may not be significant.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike_BrintonAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.