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:
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.
Posts
