Steve,
Either create a table or use a table that meets these criteria:
- Has an integer primary key
- Has values in the primary key column for every value from 1 to the
maximum number you would ever have in QTY, with no numbers missing
Assuming that RecordID is an autonumbered column, then:
INSERT INTO MY_PSEUDO_DETAIL +
( HeaderID, ItemID, ColorCD) +
SELECT HeaderID, ItemID, ColorCD +
FROM my_Pseudo_Header p1, my_Numbers_table t2 +
WHERE t2.IntegerPKIDcolumn <= p1.Qty +
AND whatever
Or, if RecordID is a sequence number within the headerID group, then:
INSERT INTO MY_PSEUDO_DETAIL +
( HeaderID, RecordID, ItemID, ColorCD) +
SELECT p1.HeaderID, t2.IntegerPKIdColumn, p1.ItemID, p1.ColorCD +
FROM my_Pseudo_Header p1, my_Numbers_table t2 +
WHERE t2.IntegerPKIDcolumn <= p1.Qty +
AND whatever
On Wed, Oct 28, 2009 at 10:42 AM, Wills, Steve <[email protected]> wrote:
> I have a table that is more-or-less a header/parent/summary table. It
> contains a column-value for quantity. I would like to use that quantity to
> insert that quantity of records into a detail table.
>
> MY_PSEUDO_HEADER
>
> HeaderID ItemID ColorCD QTY
>
> -------- ------ ------- -----
>
> 00410 ABC RD 5
>
> MY_PSEUDO_DETAIL
>
> HeaderID RecordID ItemID ColorCD
>
> -------- -------- ------ -------
>
> 00410 1 ABC RD
>
> 00410 2 ABC RD
>
> 00410 3 ABC RD
>
> 00410 4 ABC RD
>
> 00410 5 ABC RD
>
> -- And so on ...
>
>
>
> Would anyone have any tips on a good way to do this, preferably but not
> necessarily via “straight” SQL, rather than a WHILE-Loop, Cursor, Etc?
>
>
>
> Thanks,
>
> Steve (Still Around) in Memphis
>
>
>
>
>
