Insert Multiple Row into Table

Question

I am inserting data into Probale, Then I want to insert 10 rows (depend on Qty) get insert into #tbl_PckDetail,Prdno value will be same

 CREATE TABLE #Probale (prdno INT,orderno int,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int,PID int)   
  
	  INSERT INTO #Probale VALUES(10000,15,10,270,1,'2020-10-21',null,111)   


    Create table #tbl_PckDetail (DID int, prdno int,Qty int ,delid int)  

Output

Answer 1

Ok, this works:

declare @qty int = 10; -- or pass as parameter  
 DECLARE @PrdNo INT = 100;  
  
 DECLARE @Output TABLE (prdno INT, DelID INT)  
  
   insert into Probale (PrdNo, PrdQty)   
        OUTPUT Inserted.PrdNo, Inserted.DelID INTO @Output  
		values (@PrdNo, @Qty);  
  
 ;with cte as (select number, row_number() over (order by number)  as num  
 from master.dbo.spt_values where number between 1 and @qty)  
  
  
     INSERT INTO dbo.tbl_PckDetail (prdno, Qty, delid)   
     SELECT ins.prdno, 1, ins.delId		  
     FROM @Output ins  
         inner join cte on cte.num between 1 and @Qty;  

Composable DML didn't work.

Answer 2

Hi，@kkran

Welcome to Microsoft T-SQL Q&A Forum!

Experts have given you many kinds of answers, but it seems to be the easiest to implement with cte. It is recommended that you first modify the DID column in #tbl_PckDetail as an auto-increment column, IDENTITY(1,1), otherwise when making links The column is empty, otherwise only by defining another table to receive the column of DID, the same while judgment is performed, which will be more complicated.

;with cteNums(n) AS  
(  
    SELECT 1  
    UNION ALL  
    SELECT n + 1  
    FROM cteNums WHERE n < 10  
)  
INSERT .....join  #Probale  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.