sql游标

有两个表A,B 通过游标把A表取出来的数据插入到B表中.

use smjxc_fzd
go
truncate table costumepos.dbo.jb_lb
declare @stype varchar(50),@sdesc varchar(50)
declare my_cursor cursor for
select stype,sdesc
from tbSpPLXinXi
Open my_cursor
fetch my_cursor into @stype,@sdesc

while @@fetch_status=0
begin
insert into costumepos.dbo.jb_lb(kp_item_clsno,kp_item_clsname) values(@stype,@sdesc)
fetch my_cursor into @stype,@sdesc
end
close my_cursor
deallocate my_cursor
return
go

--商品档案信息

use smjxc_fzd
go
truncate table costumepos.dbo.jb_spzl
declare @barcode varchar(50),@fname varchar(50),@specs varchar(50),@sizeNum varchar

(50),@colrNum varchar(50),@unit varchar(50),
@inprc numeric(9,4),@snprc numeric(9,4),@hyrprc numeric(9,4),@pfrprc numeric

(9,4),@stype varchar(50)
declare my_cursor cursor for
select barcode ,fname ,specs ,sizeNum ,colrNum ,unit ,inprc ,snprc ,hyrprc ,pfrprc,

stype
from tbSpXinXi
Open my_cursor
fetch my_cursor into

@barcode,@fname,@specs,@sizeNum,@colrNum,@unit,@inprc,@snprc,@hyrprc,@pfrprc,@stype

while @@fetch_status=0
begin
insert into costumepos.dbo.jb_spzl

(kp_item_no,kp_item_name,kp_style_no,kp_Style_size,kp_Style_color,kp_unit_no,kp_price,kp_sale_price,kp_vip_price,kp_base_price,kp_item_clsno)
values(@barcode,@fname,@specs,@sizeNum,@colrNum,@unit,@inprc,@snprc,@hyrprc,@pfrprc,@stype)
fetch my_cursor into @barcode,@fname,@specs,@sizeNum,@colrNum,@unit,@inprc,@snprc,@hyrprc,@pfrprc,@stype
end
close my_cursor
deallocate my_cursor
return
go

--会员卡信息

use smjxc_fzd
go
truncate table costumepos.dbo.vip_info
declare @VipNO varchar(50),@VipBarCode varchar(50),@VipName varchar(50),@VipType varchar(50),@VipCardType varchar(50),@Sex varchar(50),@Birthday varchar(50),@Address varchar(50),@IDCard varchar(50),@Phone varchar(50),@Mobile varchar(50),@Sdate varchar(50),@Edate varchar(50)
declare my_cursor cursor for select
VipNO,VipBarCode,VipName,VipType,VipCardType,Sex,Birthday,Address,IDCard,Phone,Mobile,Sdate,Edate from tVipMember
Open my_cursor
fetch my_cursor into @VipNO,@VipBarCode,@VipName,@VipType,@VipCardType,@Sex,@Birthday,@Address,@IDCard,@Phone,@Mobile,@Sdate,@Edate

while @@fetch_status=0
begin
insert into costumepos.dbo.vip_info (vipID,IcNo,vipName,vipType,class,sex,Birdate,Addr,Sfz,tel,MobilTel,BeginDate,EndDate)
values(@VipNO,@VipBarCode,@VipName,@VipType,@VipCardType,@Sex,@Birthday,@Address,@IDCard,@Phone,@Mobile,@Sdate,@Edate)
fetch my_cursor into @VipNO,@VipBarCode,@VipName,@VipType,@VipCardType,@Sex,@Birthday,@Address,@IDCard,@Phone,@Mobile,@Sdate,@Edate
end
close my_cursor
deallocate my_cursor
return
go


--储值卡信息


use smjxc_fzd
go
declare @OutCardno varchar(50),@CustName varchar(50),@Sex varchar(50),@Address varchar

(50),@Phone varchar(50),@Email varchar(50),
@Psw varchar(50),@Stat varchar(50),@sFlag varchar(50)
declare my_cursor cursor for
select OutCardno,CustName,Sex,Address,Phone,Email,Psw,Stat,sFlag
from tCzkCard
Open my_cursor
fetch my_cursor into @OutCardno,@CustName,@Sex,@Address,@Phone,@Email,@Psw,@Stat,@sFlag

while @@fetch_status=0
begin
insert into costumepos.dbo.vip_info(vipID,vipName,sex,Addr,tel,EMail,pass,vipType,class)
values(@OutCardno,@CustName,@Sex,@Address,@Phone,@Email,@Psw,@Stat,@sFlag)
fetch my_cursor into @OutCardno,@CustName,@Sex,@Address,@Phone,@Email,@Psw,@Stat,@sFlag
end
close my_cursor
deallocate my_cursor
return
go

--商品库存

use smjxc_fzd
go
truncate table costumepos.dbo.kc_spkc --costumepos要对应导入的数据库
declare
@barcode varchar(30),@qty numeric(9,4),@inprice numeric(9,4)
declare qtyTmp_cur cursor for
select
tbSpXinXi.barcode,tbSpKc.Qty,tbSpKc.iprc
from tbSpKc INNER JOIN
tbSpXinXi ON tbSpKc.incode = tbSpXinXi.incode
Open qtyTmp_cur
fetch qtyTmp_cur into @barcode,@qty,@inprice

while @@fetch_status=0
begin
if @qty>0 and rtrim(@barcode)<>''
--costumepos要对应导入的数据库
insert into costumepos.dbo.kc_spkc

(kp_branch_no,kp_item_no,kp_stock_qty,kp_avg_cost,kp_last_inprice,initqty)
values('0001',@barcode,@qty,@inprice,@inprice,0)
fetch qtyTmp_cur into @barcode,@qty,@inprice

end
close QtyTmp_cur
deallocate qtyTmp_cur
return
go

--会员积分

use smjxc_fzd
go

declare
@vipid varchar(30),@jftotal numeric(9,4),@sumxftotal numeric(9,4)
declare qtyTmp_cur cursor for
select
vipno,isnull(jftotal,0),isnull(sumxftotal,0)
from tvipmember
Open qtyTmp_cur
fetch qtyTmp_cur into @vipid,@jftotal,@sumxftotal

while @@fetch_status=0
begin
--costumepos要对应导入的数据库
IF NOT EXISTS(SELECT VIPID FROM costumepos.dbo.vip_value WHERE VIPID=@VIPID)
INSERT INTO costumepos.dbo.vip_value(VIPID) VALUES(@vipid)
update costumepos.dbo.vip_value set

viplevel=@jftotal,levelsum=@jftotal,salesum=@sumxftotal
where vipid=@vipid

fetch qtyTmp_cur into @vipid,@jftotal,@sumxftotal
end
close QtyTmp_cur
deallocate qtyTmp_cur
return
go


--会员储值

use smjxc_fzd
go

declare
@vipid varchar(30),@total numeric(9,4),@cztotal numeric(9,4),@xftotal numeric(9,4)
declare qtyTmp_cur cursor for
select
incardno,isnull(total,0),isnull(cztotal,0),isnull(xftotal,0)
from tczkcard
Open qtyTmp_cur
fetch qtyTmp_cur into @vipid,@total,@cztotal,@xftotal

while @@fetch_status=0
begin
--costumepos要对应导入的数据库
IF NOT EXISTS(SELECT VIPID FROM costumepos.dbo.vip_value WHERE VIPID=@VIPID)
INSERT INTO costumepos.dbo.vip_value(VIPID) VALUES(@vipid)
update costumepos.dbo.vip_value set viplevel=0,levelsum=0,salesum=@xftotal,
pricesum=@cztotal ,vipprice=@total
where vipid=@vipid
fetch qtyTmp_cur into @vipid,@total,@cztotal,@xftotal

end

close QtyTmp_cur
deallocate qtyTmp_cur
return
go