跨服务器数据拉取

时间:2021-06-30 17:50:16

刚出来工作,遇到一个问题:在协作化生产里,咱家的系统需要调用别人ERP里面的数据,如何来实现?

分析问题,虽然是不同的系统,但是网络环境是一样的,说到底只是同局域网内跨服务器的数据库数据调用

试验过程中我模拟了一下环境,用自己的电脑当本地服务器,另外一台电脑当远程服务器,

我的思路:

1.使用DBlink,直接insert调用,再创建一个工作,定时插入数据

2.使用DBlink,在远程服务器创建一个触发器,远程表中新增一条数据,就自动往我本地服务器表里面新增数据

思路1过程:

创建链接服务器DBlink,语法:

exec sp_addlinkedserver   'DBlink名称 ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin  'DBlink名称 ', 'false ',null, '用户名 ', '密码 ' 

我的创建的名称为qqq

exec sp_addlinkedserver 'qqq' , '' , 'SQLOLEDB' , 'FDQMSSERVER\CFDB'
exec sp_addlinkedsrvlogin 'qqq' , 'false' , null , 'BISADMIN' , 'BISADMIN'

查询远程表:

select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 

试验远程库叫 学生信息管理系统 ,表明testa

select *from openquery(qqq , 'SELECT * FROM 学生信息管理系统.dbo.testa')

我需要把这个查询的结果插入我的本地表(前提是本地表和远程表的字段,表结构都一样)

直接霸王硬上弓:本地表名test

insert  test select * from  openquery(qqq, 'SELECT * FROM 学生信息管理系统.dbo.testa ')
如果创建要给工作,定时insert到本地表,会出现一个问题,
跨服务器数据拉取
那就是重复,本地表里面是设有主键的,不存在重复输入还能保存的情况。

本人不才,数据库基础也薄弱,一开始的时候没想到怎么进行筛选,只知道insert不能用distinct

后来想到一个办法,先筛选再插入,把本地表和远程表中不一样的内容筛选出来,再插入本地表。

insert test select * from qqq.学生信息管理系统.dbo.testa WHERE ID NOT IN (select ID from test.dbo.test)
然后创建要给工作,右键启动SQL Server代理

跨服务器数据拉取

新建一个作业

跨服务器数据拉取

新建一个步骤,选择本地表所在的库,把上面的代码复制到下面的命令输入框

跨服务器数据拉取

在计划里面设置拉去数据的时间间隔、频率等。

如此,那么不管远程表里面怎么新增数据,隔个设定好的时间就会新增到本地表中。

为什么不考虑到删改的情况。

在生产流程中,并不是说你数据错了就可以随便更改删除,更改也是会留下痕迹,你看到操作表单中数据是实现了更改,实际上表单的ID已经变了,也就是说新增了一条数据,只是表单上的内容没有变。所以我这边只考虑到了新增的情况。另外其实我也不知道怎么设置更改或者删除的情况,哈哈。

如果按照上述方法来设置删除,会出现一种情况,我本地库大,攒了好多条数据,远程表人家库小,装不了很多数据,会定时清理数据库,如果简单地删除我们表中不一样地数据,那么把我表中不一样的数据全部删除了,血崩。如果有更好的解决方案,我后面工作中想到了再补上。

其实ERP只能给我们几个固定字段的中间表,其他我们什么都不能操作,这也导致我后面想要的触发器不能实现。

再来说所触发器的思路。

远程库创建DBlink到本地,新建一个触发器,远程表中对数据的操作,全部同步到本地表中。

之前上CSDN发帖子,请教大神,总结了一下解决步骤

①创建远程链接,远程链接到本地,和上面的步骤差不多,谁连谁的没差别,改一下 服务器名 、用户名、密码就可以了

②只用在远程表里创建一个trigger触发器,就可以实现本地表的数据更新

语法如下:

CREATE TRIGGER tri_t ON T
    AFTER DELETE, UPDATE, INSERT
AS
    BEGIN
--判断是什么类型的服务器,再记录对应的日志
        DECLARE @IsInsert BIT ,
            @IsUpdate BIT ,
            @IsDelete BIT
        IF EXISTS ( SELECT  1
                    FROM    inserted )
            AND NOT EXISTS ( SELECT 1
                             FROM   deleted ) 
            SET @IsInsert = 1
        ELSE 
            SET @IsInsert = 0
 
        IF EXISTS ( SELECT  1
                    FROM    inserted )
            AND EXISTS ( SELECT 1
                         FROM   deleted ) 
            SET @IsUpdate = 1
        ELSE 
            SET @IsUpdate = 0
        IF NOT EXISTS ( SELECT  1
                        FROM    inserted )
            AND EXISTS ( SELECT 1
                         FROM   deleted ) 
            SET @IsDelete = 1
        ELSE 
            SET @IsDelete = 0 
 
-- 如果是插入
        IF @IsInsert = 1 
            BEGIN
 
                INSERT  INTO 链接到本地的链接名.[本地数据库名].本地表名
                        SELECT  *
                        FROM    inserted
            END
   ---更新和删除就没有写了
 
    END
我本机出了点问题,远程电脑连不上我本地电脑,这个方法还没有测试过。

虽然本次工作实施的过程中可能用不到这个,开拓一下思路也挺好,有机会的话尝试一下。