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