January 31, 2013 at 4:24 pm
Hi,
I have two tables that are exactly the same but are on two different servers. Each table has 40 fields, I want a query that will show in the result set any records where the fields are different when doing a comparison. The trouble is there is no primary key set on Either table, so not sure how to create a query.
Would the minus work or except, never used them before so don't know what to do?
Thanks
January 31, 2013 at 5:28 pm
If the two tables are exactly the same why do you need to find differences? 😀
Seriously though, what you could do is something like this:
SELECT *
FROM (
SELECT * FROM T1
UNION ALL SELECT * FROM T2) a
EXCEPT
SELECT *
FROM (
SELECT * FROM T1
INTERSECT SELECT * FROM T2) b
Note that the EXCEPT may eliminate duplicates though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 1, 2013 at 12:16 am
you can use LEFT JOIN also like
declare @t table (id int , addr nvarchar(20))
declare @t1 table (id int , addr nvarchar(20))
insert into @t
select 1, 'sdAD' UNION
select 2, 'GDFGD' UNION
select 4, 'GHJ'
insert into @t1
select 5, 'YYY' UNION
select 4, 'GHJ'
SELECT a.* FROM @t a
left JOIN @t1 b
on a.id = b.id
where b.id is null
union
SELECT b.* FROM @t a
RIGHT JOIN @t1 b
on a.id = b.id
where a.id is null
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 1, 2013 at 6:13 am
There's also a built in executable that's shipped with SQL Server. Here's the basic syntax and a link to an article[/url] that describes how to use it
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test -destinationtable table2
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply