Row Insert

I have the below temp table:

CREATE TABLE #transtmp
      (qty numeric(28,3),wmslocationid nvarchar(20),letter char(1),number int,locationavail nvarchar(20),
      boxweight numeric(28,3),boxes numeric(28))
      
INSERT into #transtmp (qty,wmslocationid,letter,number,locationavail,boxweight,boxes)
values
(1940.000,'FLOOR-A-10','A',10,'FLOOR-A-10',1940.000,1),
(5820.000,'FLOOR-B-17','B',17,'FLOOR-B-17',1940.000,3),
(1940.000,'FLOOR-C-7','C',7,'FLOOR-C-7',1940.000,1),
(3880.000,'FLOOR-B-17','B',17,'FLOOR-B-17',1940.000,2)

Current Result:
qty                  wmslocationid   letter  number    location avail      box weight    boxes
1940.000      FLOOR-A-10            A      10                 FLOOR-A-10      1940.000      1
5820.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      3
1940.000      FLOOR-C-7            C      7                 FLOOR-C-7              1940.000      1
3880.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      2

I need help in looping through the result and insert the number of rows from the boxes result.
Expected Result:
qty                  wmslocationid   letter  number    location avail      box weight    boxes   counter
1940.000      FLOOR-A-10            A      10                 FLOOR-A-10      1940.000      1              1
5820.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      3              1
5820.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      3              2
5820.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      3              3
1940.000      FLOOR-C-7            C      7                 FLOOR-C-7              1940.000      1              1
3880.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      2              1
3880.000      FLOOR-B-17            B      17                 FLOOR-B-17      1940.000      2              2
KristieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
May I ask what's the logic for that?
I mean, what for you need to replicate rows?
0
KristieAuthor Commented:
Victor, I am replicating row with different counter so I can use that to block off inventory space in an SSRS report.
0
Scott PletcherSenior DBACommented:
This is a perfect use for a tally / sequential numbers table.

Code below will generate 1M numbers (that can be increased if somehow that's needed), then use that to expand the boxes.

;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
SELECT tran.qty, tran.wmslocationid, tran.letter, tran.number, tran.location, tran.avail, tran.[box weight], tran.boxes, t.tally AS counter
FROM #transtmp tran
INNER JOIN cteTally1Mil t ON
    t.tally BETWEEN 1 AND tran.boxes
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KristieAuthor Commented:
Works perfect, Thanks Scott.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.