Solved

Visually compare data from tables in different databases

Posted on 2014-09-12
10
116 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

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 47

Expert Comment

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

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 47

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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