插入1000万数据的几种优质算法

方法一：

Declare @I int

DECLARE @sql varchar(8000)

Set @I = 0

ResetSql:

Set @sql = 'Insert into dbo.Simple values '

ComInsert:

Set @sql = @sql + '(' + CONVERT(varchar(10), @I) + ',' + CONVERT(varchar(10), Convert(int, RAND() * 2000000000)) + ')'

If @I % 300 = 299

Begin

    exec (@sql)

    Set @I = @I + 1

    Goto ResetSql

End

Set @I = @I + 1

If @I < 100000

Begin

    Set @sql = @sql + ','

    Goto ComInsert

End

 

 

方法二：

IF OBJECT_ID('dbo.Nums') IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 5000000;
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;
END