如何从两个表发送不匹配数据的警报 [英] how to send alert of unmatched data from two tables
本文介绍了如何从两个表发送不匹配数据的警报的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表,一个是生产服务器,另一个是应用服务器。我想找个工作来检查两个表的数据是否匹配?如果不匹配,也会每天发送提醒。
请帮助...
提前致谢!
解决方案
set nocount on
- 源表
声明 @ Emp1 as 表(Id int ,EName varchar ( 30 ))
插入 进入 @ Emp1 值( 1 ,' one')
insert into @ Emp1 values ( 2 ,' 两个')
插入 进入 @ Emp1 值( 3 ,' 三个')
插入 进入 @ Emp1 值( 4 ,' 四个)
insert 进入 @ Emp1 值( 5 ,' five')
insert into @ Emp1 值( 6 ,' six')
插入 进入 @ Emp1 值( 8 ,' 8')
- 目的地表
- 使用链接服务器获取此表
声明 @ Emp2 as 表( Id int ,EName varchar ( 30 ))
插入 进入 @ Emp2 values ( 1 ,' 一个')
插入 进入 @ Emp2 值( 2 ,' 两个')
insert into @ Emp2 values ( 3 ,' three')
insert 进入 @ Emp2 值( 4 ,' four')
插入 进入 @ Emp2 值( 6 ,' 6 ')
插入 进入 @ Emp2 值( 6 ,' six')
insert into @ Emp2 值( 7 ,' seven')
声明 @ Result as 表(Id int ,EName varchar ( 30 ),[表] varchar ( 30 ))
insert into @ Result
SELECT *,' Emp1' as [表] FROM ( SELECT * FROM @ Emp1 EXCEP T SELECT * FROM @ Emp2 ) AS T1
联盟 全部
SELECT *,' Emp2 ' as [表] FROM ( SELECT * FROM @ Emp2 EXCEPT SELECT * FROM @ Emp1 ) AS T2
set nocount off
如果(( select count( 1 ) from @ Result )> 0)
开始
打印 ' 不相同'
< span class =code-comment> - 使用db mail发送电子邮件警报
结束
其他
开始
打印 ' 相同'
端跨度>
I have two tables one of production server and one of application server. I want to a job to check the data of two tables is matching or not? And also send alert on daily basis if not matched.
Please help...
Thanks in advance!
解决方案
set nocount on --Source Table declare @Emp1 as Table(Id int,EName varchar(30)) insert into @Emp1 values(1,'one') insert into @Emp1 values(2,'two') insert into @Emp1 values(3,'three') insert into @Emp1 values(4,'four') insert into @Emp1 values(5,'five') insert into @Emp1 values(6,'six') insert into @Emp1 values(8,'eight') --Destination Table --Use Linked Server to get this table declare @Emp2 as Table(Id int,EName varchar(30)) insert into @Emp2 values(1,'one') insert into @Emp2 values(2,'two') insert into @Emp2 values(3,'three') insert into @Emp2 values(4,'four') insert into @Emp2 values(6,'six') insert into @Emp2 values(6,'six') insert into @Emp2 values(7,'seven') declare @Result as Table(Id int,EName varchar(30), [Table] varchar(30)) insert into @Result SELECT *, 'Emp1' as [Table] FROM (SELECT * FROM @Emp1 EXCEPT SELECT * FROM @Emp2) AS T1 Union all SELECT *, 'Emp2' as [Table] FROM (SELECT * FROM @Emp2 EXCEPT SELECT * FROM @Emp1) AS T2 set nocount off if((select count(1) from @Result)>0) begin Print 'Not identical' --Use db mail to send email alert end else begin Print 'identical' end
这篇关于如何从两个表发送不匹配数据的警报的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文