Solved

Visually compare data from tables in different databases

Posted on 2014-09-12
10
113 Views
Last Modified: 2014-09-12
I have the following code below that displays data from tables in different databases.
At the moment it displays the data from database 2 below database 1.
How can I display the data side by side as image below:
Thanks in advance for any help given.
display data side by side
/****** Visually compare data from tables in different databases  ******/
/* Database 1 */
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name
FROM CorpWear265_Restore_Test.dbo.ProductVariant O INNER JOIN CorpWear265_Restore_Test.dbo.Product C 
ON O.id = c.id 
WHERE O.Price > '0.0000'
ORDER BY O.Id ASC

/* Visually Compare code here */

/* Database 2 */
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name
FROM CorpWear265_Restore_TestAlt.dbo.ProductVariant O INNER JOIN CorpWear265_Restore_TestAlt.dbo.Product C 
ON O.id = c.id 
WHERE O.Price > '0.0000'
ORDER BY O.Id ASC

Open in new window

0
Comment
Question by:homeshopper
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40319100
you can do this:
select d1.*
, d2.*
from (
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name
, ROW_NUMBER() OVER ( ORDER BY  O.Id) RN
FROM CorpWear265_Restore_Test.dbo.ProductVariant O INNER JOIN CorpWear265_Restore_Test.dbo.Product C 
ON O.id = c.id 
WHERE O.Price > '0.0000'
ORDER BY O.Id ASC

) D1 FULL OUTER JOIN (

SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name
, ROW_NUMBER() OVER ( ORDER BY  O.Id) RN
FROM CorpWear265_Restore_TestAlt.dbo.ProductVariant O INNER JOIN CorpWear265_Restore_TestAlt.dbo.Product C 
ON O.id = c.id 
WHERE O.Price > '0.0000'
ORDER BY O.Id ASC
) d2
              
ON d1.RN = d2.RN                  

Open in new window

hope this helps
0
 
LVL 2

Expert Comment

by:Akilandeshwari N
ID: 40319114
Exactly Hengel.

Just have to change the D1 to d1 in the FROM clause.
0
 

Author Comment

by:homeshopper
ID: 40319121
Thank you for your suggestion. I get the following error:
Msg 8156, Level 16, State 1, Line 11
The column 'Id' was specified multiple times for 'd1'.
Msg 8156, Level 16, State 1, Line 21
The column 'Id' was specified multiple times for 'd2'.
Thanks in advance for the help.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40319146
If the same ID must exists in same databases and tables then you can have it simplified like this:
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name,
				O2.Id,O2.ProductId,O2.Price,O2.OldPrice,O2.OldPriceMP, O2.Description, C2.Id, C2.Name
FROM CorpWear265_Restore_Test.dbo.ProductVariant O 
	INNER JOIN CorpWear265_Restore_Test.dbo.Product C ON O.id = c.id 
	INNER JOIN CorpWear265_Restore_TestAlt.dbO2.ProductVariant O2 
		INNER JOIN CorpWear265_Restore_TestAlt.dbO2.Product C2 ON O2.id = C2.id 
	ON O.id = O2.id 
WHERE O.Price > '0.0000' AND O2.Price > '0.0000'
ORDER BY O.Id ASC

Open in new window

0
 

Author Comment

by:homeshopper
ID: 40319169
Thanks, I got that one working. Brilliant!
However, had to made small alteration to line 5 & 6
changed '.dbO2.' to 'dbo'
Thanks again for the help
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40319173
Sorry. Copy & Paste issue. I couldn't test the code.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40319176
I meant, Find & Replace issue :)
0
 

Author Comment

by:homeshopper
ID: 40319191
Hi, Below is copy of working code:
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name,
				O2.Id,O2.ProductId,O2.Price,O2.OldPrice,O2.OldPriceMP, O2.Description, C2.Id, C2.Name
FROM CorpWear265_Restore_Test.dbo.ProductVariant O 
	INNER JOIN CorpWear265_Restore_Test.dbo.Product C ON O.id = c.id 
	INNER JOIN CorpWear265_Restore_TestAlt.dbo.ProductVariant O2 
	INNER JOIN CorpWear265_Restore_TestAlt.dbo.Product C2 ON O2.id = C2.id 
	ON O.id = O2.id 
WHERE O.Price > '0.0000' AND O2.Price > '0.0000'
ORDER BY O.Id ASC

Open in new window

Is it possible to create a last column indicating a difference
between Price in database 1 & Price in database 2?
It doesn't have to be a value, just '*' will suffice.
Do I need to open new question & close this one awarding the points?
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40319198
Just add the operation as last column:
SELECT TOP 3000 O.Id,O.ProductId,O.Price,O.OldPrice,O.OldPriceMP, O.Description, C.Id, C.Name,
				O2.Id,O2.ProductId,O2.Price,O2.OldPrice,O2.OldPriceMP, O2.Description, C2.Id, C2.Name, (O.Price-O2.Price) [Price difference]
FROM CorpWear265_Restore_Test.dbo.ProductVariant O 
	INNER JOIN CorpWear265_Restore_Test.dbo.Product C ON O.id = c.id 
	INNER JOIN CorpWear265_Restore_TestAlt.dbo.ProductVariant O2 
	INNER JOIN CorpWear265_Restore_TestAlt.dbo.Product C2 ON O2.id = C2.id 
	ON O.id = O2.id 
WHERE O.Price > '0.0000' AND O2.Price > '0.0000'
ORDER BY O.Id ASC

Open in new window

0
 

Author Closing Comment

by:homeshopper
ID: 40319208
Thankyou for the suggestion, it works, Brilliant.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now