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

Transact-SQL
1

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

2

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