Question: I have the following table

TABLE A
=======

```
ID | QUANTITY
------------
1  | 3
2  | 2

```

What I need is

TABLE B
=======

```
ID | Ref No.
------------
1  | MyRef1
1  | MyRef2
1  | MyRef3
2  | AnotherRef1
2  | AnotherRef2

```

i.e. I need to generate Table B with the same number of rows as the quantity in A with an ascending ref no. on each row.

I can do it with cursors or UDFs but is there a more graceful solution?

Answer: 
This would also do the trick. It uses recursion, creates a table with rows 1-100.

```
WITH NBR ( NUM ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + NUM FROM NBR 
    WHERE NUM < 100
)
SELECT * into NUMBERS from NBR

```
A very simple way to generate @n lines is the following

```
DECLARE @n INT= 50

SELECT
    RowId   = ROW_NUMBER()OVER(ORDER BY (SELECT 0))
FROM
    (VALUES(CAST(REPLICATE('<a/>',@n) AS XML)))xmlLines(xmlLines)
    CROSS APPLY xmlLines.nodes('*')Lines(Line)

```

This method has a limit.
Since the REPLICATE function produces strings with a maximum size of 8000 characters,
the lines produced can be at most 2000.
To have a greater number you can use the following method:

```
DECLARE @n INT= 50;

WITH Block AS(
    SELECT
         RowId  = ROW_NUMBER()OVER(ORDER BY (SELECT 0))
        ,MaxBlockSize
    FROM
        (VALUES(128))MaxBlockSize(MaxBlockSize)
        CROSS APPLY (VALUES(CAST(REPLICATE('<a/>',CASE WHEN @n<MaxBlockSize THEN @n ELSE MaxBlockSize END) AS XML)))xmlLines(xmlLines)
        CROSS APPLY xmlLines.nodes('*')Lines(Line)
)
, Blocks AS(
    SELECT
         RowId          
        ,NextStart      = RowId*MaxBlockSize
    FROM
        Block
    WHERE
        RowId<=MaxBlockSize
    UNION ALL
    SELECT
         RowId          = Blocks.NextStart+Block.RowId          
        ,NextStart      = (Blocks.NextStart+Block.RowId)*MaxBlockSize
    FROM
        Blocks
        CROSS APPLY Block
    WHERE
        Blocks.NextStart+Block.RowId<=@n
)
SELECT RowId FROM Blocks ORDER BY RowId

```

the choice of the value of MaxBlockSize to 128 (maximum 2000 theoretically) has been reduced to 128 for reasons of query efficiency