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

You'll need to have a numbers table (or use a virtual tally table). Just join that with number <= tracks — James Z
– James Z, Commented Apr 29, 2016 at 3:04

Community · Accepted Answer · 2021-01-18 12:31:40Z

4

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

edited Jan 18, 2021 at 12:31

CommunityBot

11 silver badge

answered Apr 29, 2016 at 3:04

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ron tornambe Over a year ago

Taught me something I didn't know +1.

Felix Pamittan Over a year ago

@rontornambe glad I could help. You could do a lot with Tally Table. I suggest you read the referenced article.

KumarHarsh · Accepted Answer · 2016-04-29 04:20:31Z

0

Try this,I am not using any Tally Table

declare @Product_Asset table(PAId int,Tracks int)
insert into @Product_Asset values (1 ,2),(2, 3)

;with CTE as
(
select PAId,1 TrackNumber from @Product_Asset
union all

select  pa.PAId,TrackNumber+1 from @Product_Asset pa
inner join cte c on pa.PAId=c.PAId
where c.TrackNumber<pa.Tracks
)

select ROW_NUMBER()over(order by paid)id, * from cte

IMHO,Recursive CTE or sub query or using temp table performance depend upon example to example.

I find Recursive CTE more readable and won't use them unless they exhibit performance problem.

I am not convince that Recursive CTE is hidden RBAR. CTE is just syntax so in theory it is just a subquery

We can take any example to prove that using #Temp table will improve the performance ,that doesn't mean we always use temp table.

Similarly in this example using Tally Table may not improve the performance this do not imply that we should take help of Tally Table at all.

edited Apr 29, 2016 at 4:20

answered Apr 29, 2016 at 3:41

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

1 Comment

Felix Pamittan Over a year ago

Recursive CTEs do not scale very well on large data. Please read this excellent article by Jeff Moden.

Collectives™ on Stack Overflow

Generating multiple data with SQL query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related