刚出来工作,遇到一个问题:在协作化生产里,咱家的系统需要调用别人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我本机出了点问题,远程电脑连不上我本地电脑,这个方法还没有测试过。
虽然本次工作实施的过程中可能用不到这个,开拓一下思路也挺好,有机会的话尝试一下。