两表结构完全相同:
insert into table2 select * from table1 where ...
不同,或插入部分子段:
insert into table2(fid1,fid2) select fid1,fid2 from table1 where ...
insert into table2 select * from table1 where ...
不同,或插入部分子段:
insert into table2(fid1,fid2) select fid1,fid2 from table1 where ...
insert into table2 select * from table1 where ...
不同,或插入部分子段:
insert into table2(fid1,fid2) select fid1,fid2 from table1 where ...游标写法如下:
//定义游标
declare 游标名 cursor for
select fid1,fid2,... from table1 where ...//定义变量
declare @vfid1 ...
declare @vfid2 ...
...
set @vfid1=...
set @vfid2=...
...//游标运用
open cursor 游标名
fetch next from cursor into @fid1,@fid2... //取游标第一条记录的相关值
where @@fetch_status=0
begin
//其它语句,比如判断语句之类的
insert into table2(相对应的字段名) values(@fid1,@fid2...)
fetch next from cursor into @fid1,@fid2... //取下一条记录
enddellocate cursor //释放游标(dellocate可能拼错了,自己找一下)
close cursor细节的东西可以查SQL Server的帮助。
insert into table2 select * from table1 where ...
不同,或插入部分子段:
insert into table2(fid1,fid2) select fid1,fid2 from table1 where ...游标写法如下:
//定义游标
declare 游标名 cursor for
select fid1,fid2,... from table1 where ...//定义变量
declare @vfid1 ...
declare @vfid2 ...
...
set @vfid1=...
set @vfid2=...
...//游标运用
open cursor 游标名
fetch next from cursor into @fid1,@fid2... //取游标第一条记录的相关值
where @@fetch_status=0
begin
//其它语句,比如判断语句之类的
insert into table2(相对应的字段名) values(@fid1,@fid2...)
fetch next from cursor into @fid1,@fid2... //取下一条记录
enddellocate cursor //释放游标(dellocate可能拼错了,自己找一下)
close cursor细节的东西可以查SQL Server的帮助。
DECLARE @za9996 VARCHAR(10)
declare @index int
declare @newvalue varchar(10)declare mycuRsor cursor for select za0100,za9996 from ab01 where za9996='01'
OPEN MYCURSOR-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. set @index=0;
FETCH NEXT FROM mycursor
INTO @ZA0100, @ZA9996-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN -- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @ZA0100 + ' ' + @ZA9996
set @index=@index+1
print 'index:' + cast(@index as varchar(2))
set @newvalue=@ZA9996 + cast(@index as varchar(2))
update ab01 set za0100=@newvalue where za0100=@za0100 -- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM mycursor
INTO @ZA0100, @ZA9996
ENDCLOSE mycursor
DEALLOCATE mycursor