sqlserver同步数据

Question

有两个数据库，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

Answer 1

这棵树有多少数据？两个数据库所在服务器之间是什么带宽的连接？

Answer 2

b数据写个视图 跨库查询 a

Answer 3

按照B结构，使用视图查询插入更方便

Answer 4

用视图快？