[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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?
1 Solution
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.

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.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now