有两个数据库，A和B，A中的数据是树形数据，公司-楼层-房间-机柜，现在要将A中的数据同步到B中，A和B的数据库表结构不一样，要求是实时查询到数据，我做的是查询一次就在查询之前同步一次，我现在做的是执行存储过程用4个游标嵌套循环公司-楼层-房间-机柜，但是太慢了，有没有其他方法，下面是我写的循环游标
ALTER PROCEDURE [dbo].[sp_asset_data_transfer]
AS
BEGIN
declare @gsmc varchar(50)
declare @prjkey varchar(50)
declare @prjsum varchar(50)
declare @khbh varchar(50)
declare @gsdz varchar(50)
declare @sum varchar(50)
declare @gsdz_key varchar(50)
declare @gsdz_sum varchar(50)
declare @floor_key varchar(50)
declare @floor_name varchar(50)
declare @floor_sum varchar(50)
declare @floor_code varchar(50)
declare @room_key varchar(50)
declare @room_name varchar(50)
declare @room_sum varchar(50)
declare @room_code varchar(50)
declare @cabinet_key varchar(50)
declare @cabinet_name varchar(50)
declare @cabinet_sum varchar(50)
declare @cabinet_code varchar(50)
declare @key varchar(50)
declare @datatype char(1)
declare @weizhixinxi_key varchar(50)
declare @root_key varchar(50)
declare cursor_project cursor for
select prjname,prjkey from VisualNet_Project
open cursor_project
fetch next from cursor_project into @gsmc,@prjkey
while @@FETCH_STATUS=0
begin
select @root_key="key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root'
declare cursor_gsdz cursor for
select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative"= @root_key
open cursor_gsdz
fetch next from cursor_gsdz into @gsdz_key,@gsdz
while @@FETCH_STATUS=0
begin
select @sum = COUNT(1) from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
select @khbh = CustomerCode from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
if(@sum = 0)
begin
select @prjsum=COUNT(1) from Customers where gsmc = @gsmc and gsdz = @gsdz
if(@prjsum = 0)
begin
select @khbh = MAX(khbh+1) from Customers
if(@khbh is null)
begin
set @khbh = '0001'
end
else
begin
set @khbh = RIGHT(('0000'+@khbh),4)
end
insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@gsmc,@gsdz,'')
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0)
end
else
begin
select @khbh=khbh from Customers where gsmc = @gsmc and gsdz = @gsdz
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0)
end
end
else
begin
update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 0
end
fetch next from cursor_gsdz into @gsdz_key,@gsdz
end
close cursor_gsdz
deallocate cursor_gsdz
--位置信息
select @weizhixinxi_key = "key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root'
--地址
declare cursor_gsdz cursor for
select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @weizhixinxi_key
open cursor_gsdz
fetch next from cursor_gsdz into @gsdz_key,@gsdz
while @@FETCH_STATUS=0
begin
select @gsdz_sum = COUNT(1) from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0
select @gsdz = CustomerAddress from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0
if(@gsdz_sum = 0)
begin
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,@gsdz_key,@khbh,@gsdz,1,1,0)
end
else
begin
update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 1
end
--楼层
declare cursor_floor cursor for
select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @gsdz_key
open cursor_floor
fetch next from cursor_floor into @floor_key,@floor_name
while @@FETCH_STATUS=0
begin
select @floor_sum = COUNT(1) from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
select @floor_code = FloorCode from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
if(@floor_sum = 0)
begin
select @floor_code = MAX(floorcode+1) from CustomersFloor
if(@floor_code is null)
begin
set @floor_code = '000001'
end
else
begin
set @floor_code = RIGHT(('000000'+@floor_code),6)
end
select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz
insert into CustomersFloor(customercode,floorcode,floorname,floordevice) values(@khbh,@floor_code,@floor_name,0)
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@floor_key,@khbh,@gsdz,@floor_code,2,1,0)
end
else
begin
update CustomersFloor set floorname = @floor_name where floorcode = @floor_code
update VisualNetKey set SyncFlg = 1 where FloorCode = @floor_code and DataType = 2
end
--房间
declare cursor_room cursor for
select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @floor_key
open cursor_room
fetch next from cursor_room into @room_key,@room_name
while @@FETCH_STATUS=0
begin
select @room_sum = COUNT(1) from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
select @room_code = RoomCode from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
if(@room_sum = 0)
begin
select @room_code = MAX(roomcode+1) from CustomersRoom
if(@room_code is null)
begin
set @room_code = '000001'
end
else
begin
set @room_code = RIGHT(('000000'+@room_code),6)
end
select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz
insert into CustomersRoom(customercode,floorcode,roomcode,roomname,roomdevice) values(@khbh,@floor_code,@room_code,@room_name,0)
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@room_key,@khbh,@gsdz,@floor_code,@room_code,3,1,0)
end
else
begin
update CustomersRoom set roomname = @room_name where roomcode = @room_code
update VisualNetKey set SyncFlg = 1 where RoomCode = @room_code and DataType =3
end
--机柜
declare cursor_cabinet cursor for
select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @room_key
open cursor_cabinet
fetch next from cursor_cabinet into @cabinet_key,@cabinet_name
while @@FETCH_STATUS=0
begin
select @cabinet_sum = COUNT(1) from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
select @cabinet_code = CabinetCode from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0
if(@cabinet_sum = 0)
begin
select @cabinet_code = MAX(cabinetcode+1) from CustomersCabinet
if(@cabinet_code is null)
begin
set @cabinet_code = '000001'
end
else
begin
set @cabinet_code = RIGHT(('000000'+@cabinet_code),6)
end
select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz
insert into CustomersCabinet(customercode,floorcode,roomcode,cabinetcode,cabinetname,cabinetdevice) values(@khbh,@floor_code,@room_code,@cabinet_code,@cabinet_name,0)
insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@cabinet_key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,4,1,0)
end
else
begin
update CustomersCabinet set cabinetname = @cabinet_name where cabinetcode = @cabinet_code
update VisualNetKey set SyncFlg = 1 where CabinetCode = @cabinet_code and DataType =4
end
fetch next from cursor_cabinet into @cabinet_key,@cabinet_name
end
close cursor_cabinet
deallocate cursor_cabinet
declare @whrx nvarchar(200)
select @whrx = whrx from CustomersContract where khbh = @khbh
update CustomersCabinet set whrx = @whrx where customercode = @khbh
fetch next from cursor_room into @room_key,@room_name
end
close cursor_room
deallocate cursor_room
fetch next from cursor_floor into @floor_key,@floor_name
end
close cursor_floor
deallocate cursor_floor
fetch next from cursor_gsdz into @gsdz_key,@gsdz
end
close cursor_gsdz
deallocate cursor_gsdz
fetch next from cursor_project into @gsmc,@prjkey
end
close cursor_project
deallocate cursor_project
update VisualNetKey set DeleteFlg = 1 where SyncFlg = 0;
declare cursor_del cursor for
select PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,datatype from VisualNetKey where DeleteFlg = 1
open cursor_del
fetch next from cursor_del into @prjkey,@key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,@datatype
while @@FETCH_STATUS=0
begin
if(@datatype = 2)
begin
delete from CustomersFloor where floorcode = @floor_code
end
if(@datatype = 3)
begin
delete from CustomersRoom where roomcode = @room_code
end
if(@datatype = 4)
begin
delete from CustomersCabinet where cabinetcode = @cabinet_code
delete from AssetSb where lxcode = @cabinet_code
end
fetch next from cursor_del into @prjkey,@key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,@datatype
end
close cursor_del
deallocate cursor_del
update VisualNetKey set SyncFlg = 0;
END
- 5 回答
- 0 关注
- 693 浏览
添加回答
举报
0/150
提交
取消