以最快的方式生成大量的测试数据

USE tempdb
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO
CREATE TABLE #tmp(
	id bigINT IDENTITY(1,1) PRIMARY KEY,
	n  NVARCHAR(MAX)	
)
GO
SET NOCOUNT ON
DECLARE @i INT,@iMax INT
SET @iMax=10000000
WHILE 1=1 
BEGIN
	SELECT @i=COUNT(1) FROM #tmp
	PRINT @i
	IF @i>=@imax
	BEGIN
		BREAK;
	END
	IF @i=0
	BEGIN
		INSERT INTO #tmp (n) VALUES(N'xxxxxxxxxxxxxxx')	
	END
	IF @i<=@imax/2
	BEGIN
		INSERT INTO #tmp (n)
		SELECT n FROM #tmp
	END
	ELSE
	BEGIN
		INSERT INTO #tmp (n)
		SELECT TOP( @iMax-@i ) n FROM #tmp
	END
END
-- 1000 万数据也就 2 分 13 秒
-- insert into #tmp(n) values('xxxx') GO 10000000 这种方式虽然写法简单，但相当于循环一条条地插，效率不高。

——————————————————————————— 分割线 ——————————————————————————

另外也可以用 cte 来做， 虽然慢一点， 但胜在写法简单吧：

下面是两种写法实现同样功能的对比：

循环：28秒

IF OBJECT_ID('tmp') IS NOT NULL DROP TABLE tmp
GO
CREATE TABLE tmp(id bigINT NOT NULL PRIMARY KEY)
GO
SET NOCOUNT ON
DECLARE @i INT,@iMax INT
SET @iMax=10000000
INSERT INTO tmp (id) VALUES(1)
WHILE 1=1 
BEGIN
    SELECT @i=MAX(id) FROM tmp
    IF @i<=@imax/2
    BEGIN
        INSERT INTO tmp (id)
        SELECT @i+id FROM tmp ORDER BY id
    END
    ELSE
    BEGIN
        INSERT INTO tmp (id)
        SELECT TOP( @iMax-@i ) @i+id FROM tmp ORDER BY id
        BREAK;
    END
END

cte: 2分25秒

IF OBJECT_ID('dbo.spt_values') IS NOT NULL
	DROP TABLE dbo.spt_values
GO
CREATE TABLE dbo.spt_values(
	id INT NOT NULL PRIMARY KEY	
)
GO
;WITH Seq(id)  AS (
    SELECT 1
    UNION ALL 
    SELECT id + 1
	FROM   seq
	WHERE  id < 10000000
) 
INSERT INTO dbo.spt_values(id)
SELECT * FROM Seq AS s 
OPTION(MAXRECURSION 0)