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)