declare @i int set @i=1 while @i<=3 begin insert into tb(col) select @i set @i=@i+1 end
insert into tb(col) select @i插入数据的条件不是由变量@i来决定的
;with cte as( select rid=row_number over (order by getdate()),你要的其他列 from tb -- where ... )declare @i int set @i=1 while @i<=3 begin insert into tb(你要的其他列) select 你要的其他列 from cte where rid=@i set @i=@i+1 end
create table tb1(a int,b VARCHAR(100)) insert into tb1 select 1,'aaaaa' union all select 2,'bbbbb' union all select 3,'ccccc' create table tb2(a int,b VARCHAR(100))DECLARE @a int DECLARE @b VARCHAR(100) DECLARE MyCursor CURSOR FOR SELECT a,b FROM tb1
OPEN MyCursorFETCH NEXT FROM MyCursor INTO @a,@b WHILE (@@FETCH_STATUS = 0) BEGIN
--循环插入一条记录 INSERT INTO tb2(a,b) VALUES(@a,@b)
FETCH NEXT FROM MyCursor INTO @a,@b END CLOSE MyCursor DEALLOCATE MyCursor
set @i=1
while @i<=3
begin
insert into tb(col) select @i
set @i=@i+1
end
insert into tb(col) select @i插入数据的条件不是由变量@i来决定的
;with cte as(
select rid=row_number over (order by getdate()),你要的其他列 from tb -- where ...
)declare @i int
set @i=1
while @i<=3
begin
insert into tb(你要的其他列)
select 你要的其他列 from cte where rid=@i
set @i=@i+1
end
create table tb1(a int,b VARCHAR(100))
insert into tb1
select 1,'aaaaa' union all
select 2,'bbbbb' union all
select 3,'ccccc' create table tb2(a int,b VARCHAR(100))DECLARE @a int
DECLARE @b VARCHAR(100)
DECLARE MyCursor CURSOR
FOR
SELECT a,b FROM tb1
OPEN MyCursorFETCH NEXT FROM MyCursor INTO @a,@b
WHILE (@@FETCH_STATUS = 0)
BEGIN
--循环插入一条记录
INSERT INTO tb2(a,b) VALUES(@a,@b)
FETCH NEXT FROM MyCursor INTO @a,@b
END
CLOSE MyCursor
DEALLOCATE MyCursor