Solved

Visually compare data from tables in different databases

Posted on 2014-09-12
10
118 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 143

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 48

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
 
LVL 48

Expert Comment

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

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 48

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Insert where not exists 24 46
While in ##Table - Help 4 19
TSQL recursive CTE challenge... 8 29
TSQL convert date to string 4 37
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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