Based on Azure sql columns populate row of record to insert into a sub table

Transact-SQL
1

Hi,

I have master record as below. I want to append details based on the quantity:-

declare @tbl1 table(
ID int,
Qty int
)
select ID=1, Qty=5
union all
select ID=3, Qty=2
union all
select ID=7,Qty=10

I want to insert into details table

declare @subtbl1 table(
MID int,
SerialNumber nvarchar(100),
StatusID int
)

I want to write a query based on above Master table to generate the details table insert record as below:-
insert into @subtbl1
(MID,SerialNumber,StatusID)
select ID=1,'',1
union all
select ID=1,'',1
union all
select ID=1,'',1
union all
select ID=1,'',1
union all
select ID=1,'',1
union all
select ID=3,'',1
union all
select ID=4,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select ID=7,'',1
union all
select MID=7,'',1

How can I wrote this in sql to auto populate the insert part for details table?

I saw online solution below but this doesn't work in Azure SQL

CREATE TABLE #test
  (id   CHAR(1),data INT)

INSERT #test VALUES ('A',4)
INSERT #test VALUES('B',2)
INSERT #test VALUES('C',5);


SELECT s.id, 1 AS data
FROM #test s
INNER JOIN 
master.dbo.spt_values t ON t.type='P'
   AND t.number BETWEEN 1 AND s.data

Please advise.

Thanks.

Regards,
Micheale

2

hi hope this helps

please click arrow for drop create data 
declare @tbl1 table(
ID int,
Qty int
)

insert into @tbl1
select ID=1, Qty=5
union all
select ID=3, Qty=2
union all
select ID=7,Qty=10

select * from @tbl1

;WITH tally_cte
     AS (SELECT TOP 100 Row_number()
                          OVER (
                            ORDER BY (SELECT NULL)) AS rn
         FROM   sys.all_columns a
                CROSS JOIN sys.all_columns b)
SELECT b.id,
       a.rn
FROM   tally_cte a
       JOIN @tbl1 b
         ON a.rn <= b.qty
ORDER  BY b.id,
          a.rn

image
image263×590 6.23 KB

3

small correction

;WITH tally_cte
     AS (SELECT TOP 100 Row_number()
                          OVER (
                            ORDER BY (SELECT NULL)) AS rn
         FROM   sys.all_columns a
                CROSS JOIN sys.all_columns b)
SELECT 'SQL Output ', 
b.id,
       1
FROM   tally_cte a
       JOIN @tbl1 b
         ON a.rn <= b.qty
ORDER  BY b.id,
          a.rn

image
image343×609 6.61 KB

4

Hi Sir,

Brilliant, Thank you so much.

Regards,
Micheale