Question

在sqlserver2008企业管理器中批量导入数据时，提示内存不足，然后自动关闭。帮忙看看什么问题？

TRUNCATE table [QCS-DDS].[dbo].[T_DateTime]
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Set NoCount On

declare @date datetime
declare @startdate datetime
declare @enddate datetime
declare @countsum bigint
declare @count bigint
DECLARE @d Datetime
SET @d=getdate();

set @startdate = '2010-01-01 00:00:00'
set @enddate = '2010-02-01 00:00:00'
set @date = @startdate
set @count = 1
set @countsum = datediff(ss, @startdate, @enddate)
BEGIN TRANSACTION
while @count < @countsum
begin

insert into [QCS-DDS].[dbo].[T_DateTime](datetime) select @date

set @count = @count+1
set @date=dateadd(ss,1,@date)

if @count%1000=0
begin
COMMIT TRANSACTION; -- 提交事务
BEGIN TRANSACTION
end
end

COMMIT TRANSACTION; -- 提交事务

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

Answer 1

没有跳出WHILE循环，需要执行2678400次，可能是这样引起的负载高

Answer 2

把if @count%1000=0改成100试试，不行再改成10试试

Answer 3

看不上很明白呢