Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Visually compare data from tables in different databases

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
homeshopper
Asked:
homeshopper
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Akilandeshwari NCommented:
Exactly Hengel.

Just have to change the D1 to d1 in the FROM clause.
0
 
homeshopperAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
homeshopperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry. Copy & Paste issue. I couldn't test the code.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I meant, Find & Replace issue :)
0
 
homeshopperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
homeshopperAuthor Commented:
Thankyou for the suggestion, it works, Brilliant.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now