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 @Analyst_SQL ,

Check it out.

Useful link: executing-a-tsql-batch-multiple-times-using-go

SQL

USE tempdb;  
GO  
  
DROP TABLE IF EXISTS dbo.tbl_PckDetail;  
DROP TABLE IF EXISTS dbo.Probale;  
  
CREATE TABLE dbo.Probale  (  
 prdno INT,  
 orderno int,  
 CodeItem int,  
 Weigth int,  
 prdqty int,  
 EntryDate date,  
 DelID int,  
 PID INT  
);   
      
INSERT INTO dbo.Probale VALUES  
(10000,15,10,270,1,'2020-10-21',null,111);  
  
CREATE TABLE dbo.tbl_PckDetail  (  
 DID INT IDENTITY PRIMARY KEY,  
 prdno INT,  
 Qty INT,  
 delid INT  
);  
GO  

-- Method #1, in SSMS  
INSERT INTO dbo.tbl_PckDetail (prdno, Qty, delid)  
SELECT prdno, 1, NULL  
FROM dbo.Probale;  
GO 10  

-- Method #2, for stored procedure  
DECLARE @qty INT = 10; -- parameter  
  
;WITH cte_counter (number) AS  
(  
 SELECT number = 1  
 UNION ALL  
 SELECT number = number + 1  
 FROM cte_counter  
 WHERE number < @qty  
)  
INSERT INTO dbo.tbl_PckDetail (prdno, Qty, delid)  
SELECT prdno, 1, NULL  
FROM dbo.Probale CROSS JOIN cte_counter;  
  
-- test  
SELECT * FROM dbo.Probale;  
SELECT * FROM dbo.tbl_PckDetail;  

Answer 3

If you're wanting some arbitrary count based upon a parameter then you could just use a while loop.

   DECLARE @count INT  
   SET @count = 10  
     
   DECLARE @index INT  
   SET @index = 0  
   WHILE (@index < @count)  
   BEGIN  
      INSERT INTO ...  
      SET @index = @index + 1  
   END  

If you want to generate rows based upon rows in another table then a cursor might be the better option. Refer to the WHILE documentation example for what that would look like.

Answer 4

If you want to pass a parameter, then you can do

declare @qty int = 10; -- or pass as parameter
;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 prdno, 1, NULL
     FROM dbo.Probale inner join cte on cte.num between 1 and @qty;

Answer 5

Drop TABLE If exists #Probale
Drop TABLE If exists #tbl_PckDetail
CREATE TABLE #Probale (prdno INT,orderno int,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int,PID int)
Create table #tbl_PckDetail (DID int, prdno int,Qty int ,delid int)
INSERT INTO #Probale VALUES (10000,15,10,270,1,'2020-10-21',null,111)

--Use your logic to define the variable
declare @i int=10

Insert into #tbl_PckDetail(DID, prdno,Qty)
Select n, prdno,prdqty from #Probale
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) /* or use a number table if you variable is bigger */) t(n)
Where n<=@i

Select * from #Probale
Select * from #tbl_PckDetail