Insert variable number of rows, depending of a value in a row

Hello forum,

can anyone help me?
Need to insert variable number of rows in a table. The variable number depends on a specific value in the row.

Example: (Serial table showing one entry with Qty 3 of part 101, no serials stored)
Order Qty PN SN
A1 3 101 NULL

The table should be expanded like this, so i have one entry for each serial.
Qty 3 in table above should result in 3 rows.
Order Qty PN SN
A1 1 101 NULL
A1 1 101 NULL
A1 1 101 NULL

kindly regards
Wolfgam

I used a `CTE` to generate a list of numbers. If your system have a number table , you should replace this `CTE` with yours

``````    DECLARE @Source TABLE
([Order]  CHAR(3),
Qty INT,
PN INT,
SN VARCHAR(50))
INSERT INTO @Source([Order],Qty,PN,SN)
VALUES('A1', 3 ,101 ,NULL)
--,('A5', 5 ,505 ,NULL)

;WITH num_CTE
AS
(SELECT 1 AS N
UNION ALL
SELECT N+1
FROM num_CTE
WHERE N < 50
)

--SELECT * FROM num_CTE OPTION (MAXRECURSION 0)

SELECT
S.[Order]
,1 AS Qty
,S.PN
,S.SN
FROM
@Source AS S
INNER JOIN num_CTE AS V
ON V.N <= S.Qty
``````

The output for this:

``````Order Qty         PN          SN
A1    1           101         NULL
A1    1           101         NULL
A1    1           101         NULL
``````

Then you could , with a simple insert, do your stuff:

`````` ;WITH num_CTE
AS
(SELECT 1 AS N
UNION ALL
SELECT N+1
FROM num_CTE
WHERE N < 50
)
INSERT INTO TARGET([Order],Qty ,PN ,SN)
SELECT
S.[Order]
,1 AS Qty
,S.PN
,S.SN
FROM
@Source AS S
INNER JOIN num_CTE AS V
ON V.N <= S.Qty``````