SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add variable number of rows into a table based on the values in another table (without cursors/while...


Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Author
Message
charles99
charles99
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 175
The article is great. had a question: In Production, is it possible that the login that runs such a query might not have access to Master..syscolumns and so I should use some table from within my own database that i know is always 10k in size or more? Maybe a stupid because it is possible that everyone has read access to such system views. I will test but thought someone may have an answer. BOL says: "In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission."
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96867 Visits: 38988
You could build a tally table in your database an use it directly, or you could use the following as a start for building a dynamic tally table when needed with a query:



with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
eTally(n) as (select row_number() over (order by (select null)) from e2 a cross join e2 b) -- 10,000 rows




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
charles99
charles99
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 175
wow. super coding! Thanks!
Megha P
Megha P
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 2501
Hello ,
you can also try below

;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)

SELECT * FROM CTE ORDER BY docid,Pages
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96867 Visits: 38988
Megha P (3/22/2013)
Hello ,
you can also try below

;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)

SELECT * FROM CTE ORDER BY docid,Pages


Your recursive CTE will be slower and won't scale as well as the straight tally method.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42330 Visits: 20012
Lynn Pettis (3/22/2013)
Megha P (3/22/2013)
Hello ,
you can also try below

;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)

SELECT * FROM CTE ORDER BY docid,Pages


Your recursive CTE will be slower and won't scale as well as the straight tally method.


It's a clever little rCTE so let's have a look:

Code:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c)

SELECT
docid = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
Pages = (ABS(CHECKSUM(NEWID()))%4) + 1
INTO #X
FROM E4;

CREATE UNIQUE CLUSTERED INDEX ucx_docid ON #x (docid)
UPDATE STATISTICS #x WITH FULLSCAN



DECLARE @docid INT, @pages INT

PRINT ''
PRINT 'Q1 ================================================================================='
SET STATISTICS TIME, IO ON;

WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c),
Tally AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) from E4)

SELECT
x.docid,
y.pages
INTO #Q1
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = n FROM Tally) y
ORDER BY x.docid, y.Pages;

SET STATISTICS TIME, IO OFF;


PRINT ''
PRINT 'Q2 ================================================================================='
SET STATISTICS TIME, IO ON;

SELECT
x.docid,
y.pages
INTO #Q2
FROM #x x
CROSS APPLY (SELECT TOP(x.pages) pages = ROW_NUMBER() OVER(ORDER BY docid) FROM #X) y
ORDER BY x.docid, y.Pages;
SET STATISTICS TIME, IO OFF;


PRINT ''
PRINT 'Q3 ================================================================================='
SET STATISTICS TIME, IO ON;

WITH CTE AS
(
SELECT
docid, pages
FROM #x
UNION ALL
SELECT
C.docid, C.pages-1
FROM CTE C
INNER JOIN #x X
ON X.docid = C.docid
AND C.pages-1 > 0
)

SELECT
docid,
Pages
INTO #Q3
FROM CTE
ORDER BY docid,Pages;

SET STATISTICS TIME, IO OFF;
PRINT ''
PRINT '================================================================================='




Results:

Q1 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 2608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1732 ms, elapsed time = 1966 ms.

(2499713 row(s) affected)

Q2 =================================================================================
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 2, logical reads 3002608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2605 ms, elapsed time = 3546 ms.

(2499713 row(s) affected)

Q3 =================================================================================
Table 'Worktable'. Scan count 2, logical reads 19497919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#X__________________________________________________________________________________________________________________00000000008D'. Scan count 1, logical reads 4501746, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 36036 ms, elapsed time = 40989 ms.

(2499713 row(s) affected)

=================================================================================



The rCTE is a lot slower.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219216 Visits: 42002
Megha P (3/22/2013)
Hello ,
you can also try below

;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)

SELECT * FROM CTE ORDER BY docid,Pages


I'll throw in with the others on that. That's what is know as a "Counting rCTE". Another name for it is "Hidden RBAR". Please see the following article for a comparison of rCTEs to 3 other common methods. The rCTE loses on all fronts.
http://www.sqlservercentral.com/articles/T-SQL/74118/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search