ZipCode Ranges NEED HELP

[mttorpy]

I have seven original columns have to work with for solving this problem.

For every range of zip codes, I would like to break them up into single records, the problem being I have 3156 records and the ranges vary from 1 to 1000 zip codes per range.

So, I need to construct a VB script or SQL in that with create an additional record for every zip in the range and keep all information to the left of Zip1 the same for the additional records in that range.

Obviously I would no longer have a need for the zip2 column since all the zip code ranges would be broken down with corresponding pcodes.

Any ideas?

Thanks

Here is an example of two records, after the split I would need three more records created.

pcode bit Counrty state city zip1 zip2
300 0 usa al prat 36066 36068


pcode bit Counrty state city zip1 zip2
1100 0 usa mt prat 36532 36533

 

[ArtieChoke]

Read in a record.
Loop from zip1 to zip2 (convert to long first if char).
Insert a record for each one in a new table.
Repeat until complete.

In SQL, you would probably need to use a cursor - it won't be a big deal for a few thousand records (well, maybe a mil at worst case ;-)). If you're only gonna do it once, time shouldn't be a consideration (it's not like it's gonna take days to run).

In VB, use ADO to connect and get a recordset. Then you could either set another recordset with the inserts or create the insert statement directly.

"I think we're all Bozos on this bus!" - Firesign Theatre