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.
SELECT SequenceNum + 1,
WHERE SequenceNum < @RepeatCount
OPTION (MAXRECURSION 500);