12 February 2009 09:50
tonyrogerson
Duplicating data (inserting multiple rows) with a CTE
So you want to create test data without loops, here you go...
with numbsCTE( c )
as (
select c = 1
union all
select c = c + 1
from numbsCTE
where c <= 1000
)
select rowstoinsert.*
from numbsCTE
cross join (
select c1 = 1, c2 = 2, c3 = 3
) as rowstoinsert
option ( maxrecursion 0 )
Filed under: SQL Server, SQL Server FAQ