10000条内容是什么?declare @i int declare @a table(id int) set @i=1 while @<=10000 begin insert @a select @i set @i=@i+1 end
-- e.g. create table #t ( id int identity primary key, a int, b varchar(10) ) go insert into #t(a,b) select top 10000 1,'o' from pubs..employee a,pubs..employee b,pubs..employee c go select * from #t go drop table #t
批插入基本上都用insert into table from othertable
In addition to the current methods as suggested by other netters here. It is said some XML function (with 2000 version or next version I don't recall) can do batch insertion at a fraction of time taken by any current method.
insert into 表 select id=a.id+b.id+c.id+d.id from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 4 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 40 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b,( select id=0 union all select 100 union all select id=200 union all select 300 union all select id=400 union all select 400 union all select id=600 union all select 700 union all select id=800 union all select 900 ) c,( select id=0 union all select 1000 union all select id=2000 union all select 3000 union all select id=4000 union all select 4000 union all select id=6000 union all select 7000 union all select id=8000 union all select 9000 ) d order by id
declare @a table(id int)
set @i=1
while @<=10000
begin
insert @a select @i
set @i=@i+1
end
create table #t
(
id int identity primary key,
a int,
b varchar(10)
)
go
insert into #t(a,b)
select top 10000 1,'o' from pubs..employee a,pubs..employee b,pubs..employee c
go
select * from #t
go
drop table #t
select id=a.id+b.id+c.id+d.id
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 4
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 40
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
union all select id=400 union all select 400
union all select id=600 union all select 700
union all select id=800 union all select 900
) c,(
select id=0 union all select 1000
union all select id=2000 union all select 3000
union all select id=4000 union all select 4000
union all select id=6000 union all select 7000
union all select id=8000 union all select 9000
) d
order by id