1

I have two different databases that both contain a table with the same structure. I have to go across and select the distinct records across both, how is this best achieved? The uniqueness is based on Id. The other columns in the table could have varying data, but Id should be used as the basis of the distinct.

Structure is:

[Media_One].[dbo].[Video]
[Media_Two].[dbo].[Video]

2 Answers 2

4
SELECT DISTINCT col1, col2, ...
FROM
(
  SELECT col1, col2, ... FROM Media_One.dbo.Video
  UNION ALL
  SELECT col1, col2, ... FROM Media_Two.dbo.Video
) AS x;
0
4

This would suffice, since UNION already produces distinct results.

SELECT col1, col2, ... FROM Media_One.dbo.Video
UNION
SELECT col1, col2, ... FROM Media_Two.dbo.Video
1
  • 1
    Yes but note that the plan shape is quite different. UNION has a slightly higher estimated CPU cost and, in all of my tests, a slightly longer duration. Apr 1, 2016 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.