create table [TEST]
(
[NUM_ID] int primary key
)
在上面這個表中,怎樣一次性插入100萬條數據,除了以下這種方法,還可以有哪些方法?那種的效能最好?
declare @temp int
set @temp=1;
while @temp<=1000000
begin
insert into [TEST]([NUM_ID]) values(@temp)
set @temp=@temp+1;
end
go
-----------------------------------------耗時00:10:45
(
[NUM_ID] int primary key
)
在上面這個表中,怎樣一次性插入100萬條數據,除了以下這種方法,還可以有哪些方法?那種的效能最好?
declare @temp int
set @temp=1;
while @temp<=1000000
begin
insert into [TEST]([NUM_ID]) values(@temp)
set @temp=@temp+1;
end
go
-----------------------------------------耗時00:10:45
from table2 --大约1000万
MSDN的帮助,自己看一下吧,就不帖在这了
--2005
DECLARE @n AS BIGINT;
SET @n = 1000000;WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n))
),
Expand AS
(
SELECT 1 AS c
FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
FROM Expand
)
SELECT n FROM Nums WHERE n <= @n
OPTION(MAXRECURSION 0);
--2
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
--2000 这个会比前两个慢,但是前两个2000不能用
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
ENDINSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;