笔记130 几种循环插入方式的比较

 1 --几种循环插入方式的比较
 2 USE [tempdb]
 3 CREATE TABLE #TestInsert
 4 (
 5      Number INT PRIMARY KEY
 6 );
 7 GO
 8 --循环插入，不给力,我的笔记本3秒
 9 DECLARE @index INT;
10 SET @index = 1;
11 
12 WHILE @index <= 10000
13 BEGIN
14      INSERT #TestInsert(Number) VALUES( @index);
15      SET @index = @index + 1;
16 END
17 TRUNCATE TABLE #TestInsert
18 
19 
20 --放到一个事务中循环，略好，但也不是最好,我的笔记本不用1秒
21 BEGIN TRAN
22 DECLARE @index INT;
23 SET @index = 1;
24 
25 WHILE @index <= 10000
26 BEGIN
27      INSERT #TestInsert(Number) VALUES( @index);
28      SET @index = @index + 1;
29 END
30 
31 COMMIT
32 
33 --批量插入，10W行，显示0秒，有兴趣的同学改成100W行进行测试
34 INSERT #TestInsert(Number)
35      SELECT TOP (100000) rn = ROW_NUMBER() OVER
36           (ORDER BY c1.[object_id])
37           FROM sys.columns AS c1
38           CROSS JOIN sys.columns AS c2
39           CROSS JOIN sys.columns AS c3
40           ORDER BY c1.[object_id];
41 
42 
43 --CTE方式，和上面那种方式大同小异，也是批量插入,比如:
44 WITH cte AS(
45      SELECT TOP (100000) rn = ROW_NUMBER() OVER
46           (ORDER BY c1.[object_id])
47           FROM sys.columns AS c1
48           CROSS JOIN sys.columns AS c2
49           CROSS JOIN sys.columns AS c3
50           ORDER BY c1.[object_id]
51 )
52 INSERT #TestInsert(Number) SELECT rn FROM cte