Question

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

应该如何处理？
declare @i int
select @i=1
while(@i<1000000)
begin
declare @str varchar(20)
select @str=CONVERT(varchar(20),@i)
print '--'+@str
insert into book3 values('a'+@str,'b'+@str,'c'+@str,'d'+@str,'e'+@str,'2-12-2012')
set @i=@i+1
end
go
select * from book3
go

Answer 1

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

Answer 2

都没有控制COMMIT

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

Answer 3

每插入一部分，commit一下

Answer 4

分批插入

追问

我不知道怎么写，能不能帮我把代码发一下？

追答

select @i=1
while(@i<1000000)
--
select @i=1000000
while(@i<2000000)
每次插入10w