Click here to Skip to main content
15,899,126 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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!
Posted
Comments
RossMW 16-May-15 19:20pm    
Are the two server linked SQL servers? Also the method you would use would depend on the tables sizes. How big are they?

Do you need to know the differences or whether there are differences (checksums)?
SqlDevelopers2014 17-May-15 0:52am    
Thanks for the reply.
Yes they are linked servers. The size of table is 3 gb and it has a columnstore index. I have to check the previous day row i.e. getdate()-1.If there is difference the difference amount should also be mentioned in the alert.
RossMW 17-May-15 1:13am    
As they're linked have you tried to do a join to see the differences. Due to the size you would want to make sure you return just the critical information ( today and yesterday's data and critical fields) . If that works ok you can wrap in a stored procedure with an email alert via SQL job etc.

You could also use SSIS to do this as well, but if you are not familiar with it then you are best to see tutorial online first. It would not be easily to describe in a Q & A how to do it in SSIS.
SqlDevelopers2014 17-May-15 3:29am    
Thanks for the reply.
I want query to do the task in sql.
for eg,
table 1
date1 sales1 quantity1
2015-05-15 14 12
2015-05-16 10 10

table 2
date2 sales2 quantity2
2015-05-15 14 12
2015-05-16 10 15

Alert:
1. The quantity for 2015-05-15 is matching with source.
2. The quantity for 2015-05-16 is not matching. (Difference=05)

RossMW 17-May-15 3:35am    
Looks like you are just trying to check summated data. From a simplicistic point of view can you create two views to retrieve the summated data in each of the systems. Then as they are in linked servers you can create a join between the two views to get the differences. Once you can see the differences you can then put in a stored procedure for the alerting. Ie break it down to small steps and test each step

1 solution

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900