读书人

效率?效率还是效率的有关问题。使用

发布时间: 2012-02-03 22:02:47 作者: rapoo

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

读书人网 >SQL Server

热点推荐