Take one record and make multiple records by groups of 7 columns

[tcrawford]

Ok I have 1 Record from a table and I want to split that record into multiple records.

I now how to do it with a set column base, but what if my record needs to be split by every 7th column to a new record.


My table "Property" has about 212 columns per record

most of them are rates based on a date range.

For Example:


The Rate Range is from columns 1-70

And the acutual grouping for the rates is startedate, end date, rate1, rate2, rate3, rate4, nullfield

So I have 70 columns in one record that allows for 10 different "rate ranges"

I want to create a new record for every rate range:

So I need to loop a start point of column1 and grab columns 2-7 to make my first new entry.
How would I increment my start point grab every other "rate range" that has data and make a new record out of it. SO the second entry into the new database would be columns 8-14

Here is the catch I only want it to grab a "rate range" from my record and insert into my new data
base if columns 1-7 of the "rate range" have data or not null.

my new table

StartDate
End Date
rate1
Rate2
rate3
rate4
extranull field



Old Table

Col(x) = Column Number
BELOW

(Col 1 , Col 2 , Col 3, Col 4, Col 5, col 6, Col 7)
Rate Range 1 (Start date, End Date, rate1, rate2, rate3, rate4, nullfield)

(Col 8 , Col 9, Col 10,Col11, Col12, Col14)
Rate Range 2 (Start Date, End Date, Rate1, Rate2, Rate3, Rate4, NullField)

So I want to make Rate Range 1 a new entry then Rate Range 2 a new entry. Etc....

Doing this with one or 2 loop statement in coldfusion.

I am making a database system from a Symantec Q&A Export and I am Trying to get the old data into a new sql database.


Here is what I have Tried
<cfparam name=&quot;URL.next&quot; Default=&quot;47&quot;>

<cfif #url.next# EQ 158>
<CFABORT>
Done!
</cfif>



<cfset column1 = &quot;col#url.next#&quot;>
<cfset column2 = &quot;col#evaluate(&quot;URL.next+1&quot#&quot;>
<cfset column3 = &quot;col#evaluate(&quot;URL.next+2&quot#&quot;>
<cfset column4 = &quot;col#evaluate(&quot;URL.next+3&quot#&quot;>
<cfset column5 = &quot;col#evaluate(&quot;URL.next+4&quot#&quot;>
<cfset column6 = &quot;col#evaluate(&quot;URL.next+5&quot#&quot;>

<cfquery name=&quot;getinfo&quot; datasource=&quot;convert&quot;>
SELECT propertynum, propertyname, roomsize, '#column1#', '#column2#', '#column3#', '#column5#', '#column6#'
FROM property
</cfquery>


<cfif IsDefined (&quot;URL.next&quot>



<cfquery datasource=&quot;convert&quot;>
Insert into hotelRooms(hotelid, roomtype, sdaterate, edaterate, rate1, rate2, rate3, rate4, hotelname)
VALUES ('#getinfo.propertynum#', '#getinfo.roomsize#', getinfo.#column1#, getinfo.#column2#, getinfo.#column3#, getinfo.#column4#, getinfo.#column5#, getinfo.#column6#, #getinfo.propertyname#)
</cfquery>

<cfset passnum = #evaluate(&quot;URL.Next+7&quot#>

<CFLOCATION URL=&quot;

http://192.168.1.3/tom/TEST.cfm?next=#passnum#&quot;>

</cfif>