笔记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
