I want to insert multiple rows from one column.

For my POS system like this.

After that I want to see this in my table for each pallet

CodePudding user response：

You may use a recursive CTE as the following:

WITH CTE AS
  (
    SELECT ID, ItemCode, QuantityOfPalleTPerBox as  [Qty in Box], [Date],
           1 AS cn, QtyOfPallets
    FROM table_name
    UNION ALL 
    SELECT ID, ItemCode, [Qty in Box], [Date],
           cn 1, QtyOfPallets
    FROM CTE WHERE cn 1<=QtyOfPallets
  )
SELECT ID, ItemCode, [Qty in Box], [Date] FROM CTE
ORDER BY ID

See a demo.

CodePudding user response：

use this:

INSERT INTO [table2] ([ID], [Item_Code], [Qty_in_Box], [Date])
        
  (
     SELECT [ID], [Item_Code], [Quantity_Of_A_PalleT/Per_Box], [Date] FROM [table1]  
    UNION ALL
     SELECT [ID], [Item_Code], [Quantity_Of_A_PalleT/Per_Box], [Date] FROM [table1]  
    UNION ALL
     SELECT [ID], [Item_Code], [Quantity_Of_A_PalleT/Per_Box], [Date] FROM [table1]  
    UNION ALL
     SELECT [ID], [Item_Code], [Quantity_Of_A_PalleT/Per_Box], [Date] FROM [table1] 

    )

CodePudding user response：

Your question needs more clarification

But overall, you need to use an INSERT INTO SELECT command

you can find more about it by searching, but here's a quick code for your case

Repeat the SELECT statement as many times you want the row's data copied in the second table :

INSERT INTO table1 (
 ID,
 ItemCode,
 QtyInBox,
 Date 
)
(
 SELECT ID, ItemCode, QtyInBox, Date FROM table2 WHERE ID = 0001
 SELECT ID, ItemCode, QtyInBox, Date FROM table2 WHERE ID = 0001
 SELECT ID, ItemCode, QtyInBox, Date FROM table2 WHERE ID = 0001
 SELECT ID, ItemCode, QtyInBox, Date FROM table2 WHERE ID = 0001
)

This is the answer to your question according to the title, you just need to factor in the Pallet thing, and your good to go.