SQL Insert Into Multiple Records based on Serial Number

Transact-SQL
1

Hello,

I'm new to this forum, and wanted to ask for help. I'm struggling with the following issue,

I have a table Orders and a table OrderNumbers.

From the Orders Table I want to user the OrderNumber and the Quantity to insert multiple records into the OrderNumbers Table.

So if the Orders table contains:

ID              Order                               Quantity
1                Order1                                   3
2                Order2                                   2

The OrderNumber table must look like this,

ID               OrderNumber                       
1                Order1-1
1                Order1-2
1                Order1-3 
2                Order2-1
2                Order2-2

Have tried to write a query, but It still doesn't work


		SET @COUNTER = 1
		SET @QTY = 1 
	 

		 
			WHILE @COUNTER IS NOT NULL AND @COUNTER <= @QTY
			BEGIN
			 

			INSERT INTO OrderNumbers(
									ID,
									OrderNumber )
				SELECT   
						ID, 
						Order + '-' + CONVERT(nvarchar(10),@Counter)  
					FROM Orders
			
				SET @QTY = (SELECT QTY FROM OrderNumbers)

				SET @COUNTER = @COUNTER + 1;
			END;

But here the @Counter is not right, it inserts the wrong number of records.

Thanks in advance!

2

hi

hope this helps

create data script

drop table #Orders

create table #Orders( ID INT,Orders VARCHAR(100),Quantity INT)

insert into #Orders select 1 ,'Order1',3
insert into #Orders select 2 ,'Order2',2

select * from #Orders

; with tally_cte as 
(
   SELECT N=number+1 FROM master..spt_values WHERE type = 'P'
) 
select 
      ID
  ,   Orders+'-'+cast(N as varchar) 
from 
    tally_cte , #Orders 
where 
   N<=Quantity

image

3

You should use what's called a "tally" table. It's literally just a table with sequential numbers in it: 0, 1, 2, 3, ....

You should never use some system table and assume it will always have sequential numbers: you don't control that table, so you don't know what will be in it in the future. And it has a limited number for rows.

Instead create your own in-line tally table using CROSS JOINS, like this:


;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number 
    FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
)
INSERT INTO OrderNumbers ( ID, OrderNumber )
SELECT o.ID, o.OrderNumber + '-' + CAST(t.number AS nvarchar(10))
FROM dbo.Orders o
INNER JOIN cte_tally10K t ON t.number BETWEEN 1 AND o.Quantity
1 Like
4

Thanks guys! This helped me alot!