Sql Insert the same row multiple times

Question

I'm trying to write a stored procedure which will take rows from a table like the one below

and insert them as many times as the value of the Quantity column. It should also assign a unique name & number to the rows inserted.

The end result should look something like the screenshot below

I can get very close to the what I want by the SQL below Source

INSERT INTO dbo. MyTable (....)
SELECT
    t1.Name + ' (' +   CAST(E.n as VARCHAR(3)) + ')',
    @Prefix + ' - ' + ROW_NUMBER () OVER (ORDER BY t1.Name )
FROM
    MyFirstTable t1
    JOIN ....
    JOIN .....
CROSS JOIN
    (SELECT TOP 500 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns)E(n)
WHERE 
    E.n <= t1.Quantity
    AND....

The above statement works because I do know that quantity will never exceed 500 but I'm not a big fan of the way it is done. Is there a better way to accomplish this?

I'm not very experienced in sql.

Answer 1

Seems like you have already figured out what you need for the most part. As far as the top 500 not exceeding goes, you could either leave it there or remove it. I think this is what you may be looking for:

SELECT
    id,        --not sure where this id comes from but looks different in your output
    CASE 
      WHEN E.n-1 > 0
        THEN t1.Name + ' (' +   CAST(E.n-1 as VARCHAR(3)) + ')'
     ELSE t1.Name
     END as  Name,
    @prefix + ' - ' + cast(ROW_NUMBER () OVER (ORDER BY t1.id) as varchar(10)) as Number
FROM
    test t1
    JOIN ...
    JOIN ...
CROSS JOIN
    (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns)E(n)
WHERE 
    E.n <= t1.Quantity
    AND ....;

SQL Fiddle Demo