x

how to create sql query to get difference from two different tables from different databases ?

I created SQl which shows the different data counts between two different databases ( selected tables) but I want to see data instead of counts.

These two databases are running on two different servers.

Below is query

 Declare @ReportDate1 datetime
 Declare @ReportDate2 datetime
 Declare @ReportDate3 datetime
 Declare @sp_date datetime
 
 SET @sp_Date = convert(varchar(10), getdate()-1,120)
 
 
 set @ReportDate1 = '2016-02-29 00:00:00.000'
 set @ReportDate2 = '2016-02-29 07:00:00.000'
 set @ReportDate3 = '2016-02-29 23:31:00.000'
 
 
 
 --set @ReportDate1 = convert(varchar(10), getdate()-1,120)
 --set @ReportDate2 = convert(varchar(10), getdate()-1,120)
 --set @ReportDate3 = convert(varchar(10), getdate(),120)
 
 
 select @ReportDate1 as DateR,a.orderBook,a.ShortName,a.Test_Trade_Count,b.Test_TradeCount,(a.Test_Trade_Count - b.Test_TradeCount) as Diff  from (
 select A.orderBook,B.ShortName,Count(*) as Test_Trade_Count 
 from [dbo].[trdTrades] A,[trdContractMaster] B
 where A.BusinessDay=@ReportDate1 and A.buyOrSell='B' and A.orderBook=B.OrderbookID
 group by  A.orderBook,B.ShortName ) a
 --order by 3 desc
 Join (
 
 Select TradeEvents.OrderBook , TradableInstruments.Name,COUNT(*) as Test_TradeCount 
 from [10.10.10.20].[DGCX_TestdData].[dbo].TradeEvents,[10.10.10.10].[DGCX_TestdData].[dbo].TradableInstruments
 where TradeEvents.OrderBook=TradableInstruments.OrderbooId
 and TradableInstruments.IsSpread=0
 AND
 TradeEvents.TimeOfEvent between @ReportDate2 and @ReportDate3
 and TradeEvents.SubTypeOfTrade = 6 
 --and OrderBook=1729
 group by TradeEvents.SubscriptionGroup,TradableInstruments.Name,TradeEvents.OrderBook ) b
 
 on a.orderBook = b.OrderBook
 order by 6 desc
more ▼

asked Mar 01, 2016 at 08:48 AM in Default

avatar image

omi
11 2

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.

Mar 08, 2016 at 04:57 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Say you have two tables and your hypothesis is that they are identical in every respect, and you want to test that hypothesis. There are three things to consider.

  1. Do they have the same columns (same number of columns and same data types in the same order).

  2. Do they have the same rows (same pks)

  3. Does each cell have the same content?

To test all this is simpler than you might think. Remember you're working with sets.

 SELECT *
 FROM dbo.table1
 EXCEPT
 SELECT * 
 FROM dbo.table2

and then...

 SELECT *
 FROM dbo.table2
 EXCEPT
 SELECT * 
 FROM dbo.table1

If these exercises produce zero rows, and no errors you have identical tables. Assuming you have identical columns, your output will be all the rows that don't match in some way.

more ▼

answered Mar 01, 2016 at 12:28 PM

avatar image

GPO
4.9k 41 51 58

Sorry for late reply, but thanks for your reply. It's work for me

Mar 23, 2016 at 02:21 PM omi
(comments are locked)
10|1200 characters needed characters left

The basic technique for returning non-matching lines is quite simple:

 DECLARE @a TABLE ( id INT, val VARCHAR(MAX) );
 
 DECLARE @b TABLE ( id INT, val VARCHAR(MAX) );
 
 INSERT  INTO @a
 VALUES  ( 2, 'prime' ), ( 3, 'prime' ), ( 5, 'prime' );
 INSERT  INTO @b
 VALUES  ( 1, 'one' ),   ( 2, 'two' ),   ( 3, 'three' ), ( 4, 'four' );
 
 SELECT  *
 FROM    @a a
         FULL OUTER JOIN @b b ON [b].id = [a].id
 WHERE   a.id IS NULL
         OR b.id IS NULL;

This returns a data set:

 id    val    id    val
 5     prime  NULL  NULL
 NULL  NULL   1     one
 NULL  NULL   4     four

This gives the items in @a that don't have a corresponding item in @b

As for doing it with your data... I'm leaving that to you. Sorry.

more ▼

answered Mar 01, 2016 at 09:04 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x-235

asked: Mar 01, 2016 at 08:48 AM

Seen: 62 times

Last Updated: Mar 23, 2016 at 02:21 PM

Copyright 2017 Redgate Software. Privacy Policy