sql server 执行循环插入100万条数据,当插入10万条左右时内存溢出了

CREATE TABLE #ta(A1 int,ID bigint NOT NULL IDENTITY(1,1));go
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO #ta(A1) select 1
WHILE @rc < ceiling(log(@max) / log(2))
BEGIN
INSERT INTO #ta(A1) SELECT 1 FROM #ta;
SET @rc = @rc + 1 ;
END
select @max = @max - @@IDENTITY

INSERT INTO #ta(A1)
SELECT 1 FROM #ta where id <= @max

insert into book3
select 'a'+ltrim(str(id)),'b'+ltrim(str(id)),'c'+ltrim(str(id)),'d'+ltrim(str(id)),'e'+ltrim(str(id)),'2-12-2012'
from #ta

都没有控制COMMIT

每1W条记录COMMIT一次就OK了

每插入一部分，commit一下

分批插入