效率？效率，还是效率的问题。。。。使用游标？
做循环插入。这种插入使用游标是不是效率高呢?该如何写？
---------
@chk_start = '1111111 '
@chk_end = '2211111 '
while @chk_start <= @chk_end
BEGIN
INSERT INTO [table] (
[id],
[date]
)VALUES (
@chk_start,
getdate()
)
set @chk_start = cast(@chk_start as numeric) + 1
END
[解决办法]
数据很少的话无所谓，这么多的话不要用游标
可以用临时表，比如
SELECT TOP 100 IDENTITY (INT,1,1) AS IDENT INTO #B FROM table
然后再计算，两三个SQL就够了
[解决办法]
怎么用游标?
可以考虑这样,效率是可以的
@chk_start = '1111111 '
@chk_end = '2211111 '
INSERT INTO [table] (
[id],
[date]
)
select
cast(@chk_start as int)+a.a+b.b*10+c.c*100+d.d*1000+e.e*10000+f.f*100000+g.g*1000000,
getdate()
from (
select 0 as a
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as a,(
select 0 as b
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as b,(
select 0 as c
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as c,(
select 0 as d
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as d,(
select 0 as e
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as e,(
select 0 as f
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
) as f,(
select 0 as g
union all
select 1
) as g
where cast(@chk_start as int)+a.a+b.b*10+c.c*100+d.d*1000+e.e*10000+f.f*100000+g.g*1000000 <=cast(@chk_end as int)
[解决办法]
declare @chk_start int
declare @chk_end int
declare @i int
declare @s varchar(200)
select
@chk_start = 6,
@chk_end = 15,
@i = @chk_end - @chk_start + 1,
@s = 'select top '
+ convert(varchar(10),@i)
+ ' id = identity(int, '
+ convert(varchar(10),@chk_start) + ',1),date = getdate()
into #t
from sysobjects,syscolumns,sysindexes
select * from #t
drop table #t '
exec(@s)
/**
id date
----------- -----------------------
6 2007-06-19 15:51:36.850
7 2007-06-19 15:51:36.850
8 2007-06-19 15:51:36.850
9 2007-06-19 15:51:36.850
10 2007-06-19 15:51:36.850
11 2007-06-19 15:51:36.850
12 2007-06-19 15:51:36.850
13 2007-06-19 15:51:36.850
14 2007-06-19 15:51:36.850
15 2007-06-19 15:51:36.850
**/
[解决办法]
select Top 1100000 ident=identity(1111111,1) into #Table_Pqs from Sysobjects A,Sysobjects B
Insert Into table([id],[date]) Select ident,getdate() from #Table_Pqs
--如果时间不是按照插入时间有要求的话,将时间赋值给变量后写入将更快（如下）
/*
declare @Date datetime
select @date=getdate()
Insert Into table([id],[date]) Select ident,@date from #Table_Pqs
*/
Drop table #Table_Pqs
