Generating multiple data with SQL query

Question

I have 2 tables as below

Product_Asset:

PAId      Tracks
1          2
2          3

Product_Asset_Resource:

Id        PAId        TrackNumber
1          1              1 
2          1              2 
3          2              1
4          2              2 
5          2              3  

I would like to know if I can generate the data in product_asset_resource table based on product_asset table using TSQL query (without complex cursor etc.)

For example, if the number of tracks in product_asset is 3 then I need to populate 3 rows in product_asset_resource with track numbers as 1,2,3

Answer 1

You can do this with the help of a Tally Table.

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(SELECT MAX(Tracks) FROM Product_Asset) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
)
SELECT
    Id  = ROW_NUMBER() OVER(ORDER BY pa.PAId, t.N),
    pa.PAId,
    TrackNumber = t.N
FROM Product_Asset pa
INNER JOIN CteTally t
    ON t.N <= pa.Tracks

ONLINE DEMO