为何要20次,有啥规律?declare @i int set @i = 1 while @i <= 20 begin insert into b select id from a set @i = @i + 1 end
insert 表2 select a.ID from 表1 as a,(select top 20 ID from syscolumns) as b where a.ID=a這樣?
双循环 declare @id varchar(10),@n int=1 ,@i int=1 while exists(select 1 from (select *,ROW_NUMBER() over(order by getdate()) rid from tb)a where rid=@n) begin select @id=id from tb where rid=@n while @i<=20 begin insert into b select id from a set @i = @i + 1 end set @n=@n+1 end
declare @id declare @cursor_Insert cursor for select Id from table1 open @cursor_Insert fetch next from @cursor_Insert into @id
while @@FETCH_STATUS=0 begin ..插入20行数据. fetch next from @cursor_Insert into @id end close @cursor_Insert deallocate @cursor_Insert
if object_id('t1') is not null and object_id('t2') is not null drop table t1,t2 create table t1(id int,num varchar(10)) create table t2(id int,num varchar(10)) insert t1 select 1,'12' union all select 2,'132' go -- declare @i int -- set @i=1 -- while @i<21 -- begin -- insert into t2 select * from t1 -- set @i=@i+1 -- end insert into t2 select * from (select id,num from t1,master..spt_values where number<20 and type='p') as aselect * from t2 order by id
set @i = 1
while @i <= 20
begin
insert into b select id from a
set @i = @i + 1
end
select
a.ID
from 表1 as a,(select top 20 ID from syscolumns) as b
where a.ID=a這樣?
while exists(select 1 from (select *,ROW_NUMBER() over(order by getdate()) rid from tb)a where rid=@n)
begin
select @id=id from tb where rid=@n
while @i<=20
begin
insert into b select id from a
set @i = @i + 1
end
set @n=@n+1
end
declare @cursor_Insert cursor for
select Id from table1
open @cursor_Insert
fetch next from @cursor_Insert into @id
while @@FETCH_STATUS=0
begin
..插入20行数据.
fetch next from @cursor_Insert into @id end
close @cursor_Insert
deallocate @cursor_Insert
表1为pointhistory表,表结构:id routehistoryid pointname membername
表2为itemhistory表,表结构:id routehistoryid pointhistoryid pointname itemname
现在想从表1中取id(即表2的pointhistoryid)生成20条数据插入表2中,表2的id可以是随机的,但不能为空和重复,pointname,membername,itemname分别取自其他3张表,不过这里只用他们的名字,请问要怎么实现
if object_id('t1') is not null and object_id('t2') is not null
drop table t1,t2
create table t1(id int,num varchar(10))
create table t2(id int,num varchar(10))
insert t1
select 1,'12' union all
select 2,'132'
go
-- declare @i int
-- set @i=1
-- while @i<21
-- begin
-- insert into t2 select * from t1
-- set @i=@i+1
-- end
insert into t2 select * from (select id,num from t1,master..spt_values where number<20 and type='p') as aselect * from t2 order by id
表1的内容为id routehistoryid pointname membername
2541 451263252 发电机 李明现在取id添加到表2中,id routehistoryid pointhistoryid pointname itemname
12345 451263252 2541 发电机 温度1
12456 451263252 2541 发电机 温度2
451256 451263252 2541 发电机 温度3
…… …… …… …… ……这样应该能比较清楚了吧
你这个不是简单的循环20次,可能需要多表进行联合查询,比如说你的"温度1"和"温度2"应该是查询其他表生成的
如果你只是想简单的循环20次,如下
if object_id('t1') is not null and object_id('t2') is not null
drop table t1,t2
create table t1(id int,routehistoryid varchar(20),pointname varchar(20),membername varchar(10))
insert t1 select 2541,'451263252','发电机','李明'create table t2(
id int identity(1,1) primary key,
routehistoryid varchar(20),
pointhistoryid int,
pointname varchar(20),
itemname varchar(20))
go
--
insert into t2 select routehistoryid,id as pointhistoryid,pointname,name as itemname from (select id,routehistoryid,pointname,name from t1,master..spt_values where number<20 and type='p') as aselect * from t2 order by id
/*
id routehistoryid pointhistoryid pointname itemname
---------------------------------------------
1 451263252 2541 发电机 NULL
2 451263252 2541 发电机 NULL
3 451263252 2541 发电机 NULL
4 451263252 2541 发电机 NULL
5 451263252 2541 发电机 NULL
6 451263252 2541 发电机 NULL
7 451263252 2541 发电机 NULL
8 451263252 2541 发电机 NULL
9 451263252 2541 发电机 NULL
10 451263252 2541 发电机 NULL
11 451263252 2541 发电机 NULL
12 451263252 2541 发电机 NULL
13 451263252 2541 发电机 NULL
14 451263252 2541 发电机 NULL
15 451263252 2541 发电机 NULL
16 451263252 2541 发电机 NULL
17 451263252 2541 发电机 NULL
18 451263252 2541 发电机 NULL
19 451263252 2541 发电机 NULL
20 451263252 2541 发电机 NULL
*/
drop table t1,t2