日期:2014-05-19  浏览次数:20623 次

效率?效率,还是效率的问题。。。。使用游标?
做循环插入。这种插入使用游标是不是效率高呢?该如何写?
---------
@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