Repeating rows of data in SQL Server 2005

I ran into this issue at work; I needed to repeat the same data 500 times or so, with each row being exactly the same. After an unsuccessful Googling, I thought I’d share my solution to the problem using Common Table Expression (CTEs).
NOTE: CTEs are not necessarily the best solution for this problem, as they rely on the MAX_RECURSION value being greater than or equal to the number of rows needed.

Basing my solution on @generating a sequence of numbers@, I simply created a sequence of numbers in one column; the other columns I filled with my repeated data. Then in the SELECT statement I ignore the column with the sequential number.


WITH RepeatingValues
(
SequenceNum,
Value1,
Value2,
Value3
)
AS
(
SELECT 1,
@Value1,
@Value2,
@Value3
UNION ALL
SELECT SequenceNum + 1,
Value1,
Value2,
Value3
FROM NewValues
WHERE SequenceNum < @RepeatCount
)
SELECT Value1,
Value2,
Value3
FROM NewValues
OPTION (MAXRECURSION 500);

Leave a Reply

Your email address will not be published. Required fields are marked *