sqlserver同步数据

悬赏园豆：50 [已解决问题] 解决于 2016-06-12 17:11

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

sql server 数据同步

小光 | 小虾三级 | 园豆：1766
提问于：2016-06-12 09:51

< >

最佳答案

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

收获园豆：48

爱编程的大叔 | 高人七级 |园豆：30839 | 2016-06-12 16:14

目前是在本机上，在测运行速度

A和B是两个系统的两个数据库，数据库表不一致，B系统中功能已经写好了，现在是把A中的数据处理以后放到B系统数据库里面直接用B系统数据库

用游标嵌套游标太慢了，有没有更好的方法

小光 | 园豆：1766 (小虾三级) | 2016-06-12 16:19

@小光:

这棵树有多少数据？

太慢了有多慢？

有的人一年太慢，有的人一秒太慢。所以这样是无法交流沟通的。

爱编程的大叔 | 园豆：30839 (高人七级) | 2016-06-12 16:29

@爱编程的大叔: 目前30w测试数据，跑半个小时都跑不完，直接停止了

小光 | 园豆：1766 (小虾三级) | 2016-06-12 16:32

@小光: 谁让你这样做的方案？查询前同步一下...我还以为30条数据呢。

爱编程的大叔 | 园豆：30839 (高人七级) | 2016-06-12 16:50

@爱编程的大叔: 客户想要实时的，领导和客户讨论以后让做。。。但是B系统是查本地数据库的，两个数据库表结构还不一样，总不能重写功能再远程查询，所以查询之前同步一下，但是太慢了

小光 | 园豆：1766 (小虾三级) | 2016-06-12 16:56

@小光: 你还是考虑换公司吧，这届领导不行。

"总不能重写功能再远程查询"

这话谁告诉你的，真要实时，还只能这样。

爱编程的大叔 | 园豆：30839 (高人七级) | 2016-06-12 17:01

@爱编程的大叔: 领导说重写功能这么做浪费时间，还会出新bug，唉，最近来了个新项目，看看那个项目怎么样，实在不行再换吧，都说经常换公司不好，不敢换了

小光 | 园豆：1766 (小虾三级) | 2016-06-12 17:04

@小光: 所以我说这届领导不行啊。

问题是你们领导。

要么你问他有啥好方法，也许他真有好办法呢。

爱编程的大叔 | 园豆：30839 (高人七级) | 2016-06-12 17:09

@爱编程的大叔: 嗯

小光 | 园豆：1766 (小虾三级) | 2016-06-12 17:10

其他回答(2)

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

收获园豆：1

小眼睛老鼠 | 园豆：2731 (老鸟四级) | 2016-06-12 10:51

A和B是两个系统的两个数据库，数据库表不一致，B系统中功能已经写好了，现在是把A中的数据处理以后放到B系统数据库里面直接用B系统数据库

支持(0) 反对(0) 小光 | 园豆：1766 (小虾三级) | 2016-06-12 16:16

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

收获园豆：1

CodeHsu | 园豆：5468 (大侠五级) | 2016-06-12 13:50

用视图快？

支持(1) 反对(0) 小光 | 园豆：1766 (小虾三级) | 2016-06-12 16:17

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

sqlserver同步数据

欢迎，请先登录或者注册。