做循环插入。这种插入使用游标是不是效率高呢?该如何写?
---------
@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
---------
@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_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 IDENTITY (INT,1,1) AS IDENT,' ' AS INDX, DATA INTO #B FROM DATA_TABLE--计算ID
UPDATE #B
SET INDX=CONVERT(VARCHAR(7),'1111111'+IDENT)--INSERT
INSERT INTO [table]
SELECT INDX, DATA FROM #B
select Top 1100000 ident=identity(1111111,1) into #Table_Pqs from Sysobjects A,Sysobjects BInsert 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
select Top 1100000 ident=identity(1111111,1) into #Table_Pqs from Sysobjects A,Sysobjects B
里的1100000 是2211111 与1111111的差值,可以写更大数值那么Insert Into table([id],[date]) Select ident,getdate() from #Table_Pqs就需要增加条件
where ident<=2211111也可以
select Top 1100000 ident=identity(1111111,1) into #Table_Pqs from Sysobjects
A,Sysobjects B
有错identity(1111111,1) 换成identity(int,1111111,1)还有,你这个数据比较大用Sysobjects可能不行!那就换成SysColumns
select @a='001234567891234',@b='001234577891234',
@c=cast(@b as numeric)-cast(@a as numeric)select Top @c ident=identity(numeric,@a,1) into #Table_Pqs from SysColumns A,SysColumns B 这里提示Top @c有错误,因为是变量???
例如:
Create table #Table_Pqs(ident int identity(1,1),c int)
declare @Sql varchar(8000)
set @Sql='insert into #Table_Pqs(c) Select Top '+ cast(@c as varchar) +' 1 from SysColumns A,SysColumns B'Exec(@Sql)就可以了(临时表里定义c int 是为了可以写入数据用而已,无实际应用价值)