sql游标

declare id_cursor
CURSOR  FOR  SELECT id  FROM #t1 ----创建游标
Open id_cursor  ---打开游标
declare @id int,@S varchar(100), @S1 varchar(100),@S2 varchar(100),@S3 varchar(100),@publisher nvarchar(255),@addre nvarchar(255),@postcode float,@phone nvarchar(255)
fetch next from id_cursor into @id  ---游标第一次移动并复制给参数
select @S=出版者前缀,@publisher=出版者,@addre=地址,@postcode=邮编,@phone=联系电话
from #t1
where id = @id
select @S3= right(@S,charindex('-',REVERSE(@S))-1)
select @S2 = SUBSTRING(@S,5,1)
select @S1=   SUBSTRING(@S,0,4)
insert into app_Publishers_Original(EANUCC,GroupNumber,PublisherNumber,Publisher,addr,PostCode,Phone)
values(@S1,@S2,@S3,@publisher,@addre,@postcode,@phone)
while @@FETCH_STATUS = 0   ---循环判断游标还在读取
begin
 fetch next from id_cursor into @id
 select @S=出版者前缀,@publisher=出版者,@addre=地址,@postcode=邮编,@phone=联系电话
 from #t1
 where id = @id
 select @S3= right(@S,charindex('-',REVERSE(@S))-1)
 select @S2 = SUBSTRING(@S,5,1)
 select @S1=   SUBSTRING(@S,0,4)
 insert into app_Publishers_Original(EANUCC,GroupNumber,PublisherNumber,Publisher,addr,PostCode, Phone)
 values(@S1,@S2,@S3,@publisher,@addre,@postcode,@phone)
end
close id_cursor    ----关闭游标
deallocate id_cursor  ----删除游标

转载于:https://www.cnblogs.com/happygx/archive/2011/02/18/1957983.html