CODEWITHSUNDEEP
sqlsql-serversql-server-2019
user3666570
user3666570

Reputation: 37

How to insert multiple rows from a single row?

How do I copy few rows from one SQL Server table into many rows in another? Table1 has a [Qty] column. I want to copy every row from Table1 into Table2, each row copied [Qty] times.

Sample data:

crdCard:

CardID  Qty  CardName
------------------------
   1     4   Lose A Turn
   2     3   Collect $100
   3     2   Bonus Roll
   4     1   Golden Parachute

This represents 10 cards. I want to copy into another table as 10 rows:

crdShuff

ShuffID   RoundID   CardID
-------------------------------
   1        1           1   (Lose A Turn)
   2        1           1   (Lose A Turn)
   3        1           1   (Lose A Turn)
   4        1           1   (Lose A Turn)
   5        1           2   (Collect $100)
   6        1           2   (Collect $100)
   7        1           2   (Collect $100)
   8        1           3   (Bonus Roll)
   9        1           3   (Bonus Roll)
  10        1           4   (Golden Parachute)

My procedure:

crdShuffleDeck
begin
   declare @RoundID int

   set @RoundID = isnull((select max(RoundID) from crdShuffle), 0) + 1

   -- TEMP TABLE...
   create table #temp1(CardID int, Qty int)

   -- SEED...
   select @CardID = min(CardID) from crdCard

   -- LOOP ALL CARDS...
   while (@CardID > 0)
   begin
      -- GET QTY...
      select @Qty = Qty from crdCard where CardID = @CardID

      -- INSERT THAT MANY TIMES...
      while (@Qty > 0)
      begin
         -- ADD CARD ROW...
         insert into #temp1(CardID, Qty)
         values(@CardID, @Qty)

         -- DECREMENT...
         set @Qty = @Qty - 1
      end

      -- FIND NEXT CARD...
      set @CardID = isnull((select min(cd.CardID) from crdCard cd where cd.CardID > @CardID), 0)
   end

   -- DEBUG... * THIS RETURNS THE CORRECT @RoundID (1) & NUMBER OF ROWS (10). *
   select t.CardID
        , @RoundID
   from #temp1 t

   -- SHUFFLE INTO PERMANENT TABLE...
   insert into crdShuffle(RoundID
                        , CardID)
   select @RoundID
        , t.CardID
   from #temp1 t
   --order by NEWID()

end

My real data is 29 rows defining 114 cards. This procedure returns 228 or 342 rows seemingly randomly. Rows returned include Round #1 and Round #2 which I assume can only happen if this procedure is called twice or somehow calls itself. When it returns 342 rows there's still only Round #1 and #2, no #3s.

I've tried the SHUFFLE INTO PERMANENT TABLE portion as a loop too, copying one row at a time. No matter what I do I end up with 2x or 3x rows.

I'm utterly stumped. I am convinced it's a quirk/bug of SQL Server.

Upvotes: 1

Views: 90

Answers (2)

T N
T N

Reputation: 10205

If using SQL Server 2022 or later, you can use GENERATE_SERIES() to act as a row multiplier.

SELECT
    ROW_NUMBER() OVER(ORDER BY C.cardId, S.value) as ShuffID,
    @RoundID AS RoundID,
    C.CardID,
    C.CardName
FROM crdCard C
CROSS APPLY GENERATE_SERIES(1, C.Qty) S
WHERE C.Qty > 0  -- Needed if Qty might be zero

The ROW_NUMBER() calculation for ShuffID is included for demo purposes. Omit that select item if ShuffID is an IDENTITY column in the target table.

Results:

ShuffID RoundID CardID CardName
1 1 1 Lose A Turn
2 1 1 Lose A Turn
3 1 1 Lose A Turn
4 1 1 Lose A Turn
5 1 2 Collect $100
6 1 2 Collect $100
7 1 2 Collect $100
8 1 3 Bonus Roll
9 1 3 Bonus Roll
10 1 4 Golden Parachute

See this db<>fiddle for a demo of the above SELECT or this one showing repeated executions that insert into crdShuffle.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522509

We can handle your requirement using a join approach with the help of a sequence table. Consider the following CTE as the sequence table:

WITH seq AS (
    SELECT 1 AS qty UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
    -- and so on, as many quantity values are needed
)

SELECT
    ROW_NUMBER() OVER (ORDER BY c.CardID) AS ShuffID,
    1 AS RoundID,
    c.CardID
FROM crdCard c
INNER JOIN seq s
    ON s.qty <= c.Qty
ORDER BY
    c.CardID;

Upvotes: 3

Related Questions