Recursive Common Table Expressions

Problem:  I need to load a lot of dummy data into a table and time is limited.

Solution:  Common Table Expressions (CTE) used in a recursive fashion.  Here is an excellent demo on how CTE’s can be used recursively:

 http://www.simple-talk.com/content/print.aspx?article=260

Example:

SET NOCOUNT ON

CREATE TABLE [sqlCTE]([field_bigint] [bigint] NULL) ON [PRIMARY]

GO

DECLARE @s DATETIME

DECLARE @i int

SET @i = 0

SET @s = getdate()

WHILE (@i <= 1024032)

BEGIN

      INSERT INTO [sqlCTE]([field_bigint]) VALUES (@i)

      SET @i = @i + 1

END

PRINT datediff(ss,@s,getdate())

GO

TRUNCATE TABLE sqlCTE

GO

DECLARE @s DATETIME

SET @s = getdate();

with n (j)

as

(

select j = 0

union all

select j = j + 1 from n where j < 32000

)

INSERT INTO [sqlCTE]([field_bigint])

select na.i

from ( select i = j + k

   from n

   cross join

   ( select k = j * 32001

      from n

      where j < 32

   ) n2

) na

option (maxrecursion 32000)

PRINT datediff(ss,@s,getdate())

GO

Results:

297

5

So, to load 1,024,032 records:

1)      With a WHILE loop: 4min and 57seconds

2)      With a recursive CTE: 0min and 5seconds.

You do the math. 

Thanks to my colleague who introduced me to recursive CTEs and to Nigel Rivett  of Simple-Talk (http://www.simple-talk.com/) for the thorough explanation of the CTE structure (http://www.simple-talk.com/content/print.aspx?article=260)

Also, check out the SQL Server Template Explorer (Ctrl+Alt+T in SSMS) in the “Recursive Queries” folder for more examples from Microsoft.