Visually compare data from tables in different databases

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                  

Select allOpen in new window

hope this helps

Exactly Hengel.

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

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.

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

Select allOpen in new window

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

Sorry. Copy & Paste issue. I couldn't test the code.

I meant, Find & Replace issue :)

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

Select allOpen 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?

Thankyou for the suggestion, it works, Brilliant.