Find the difference from two table in two different database, if Column name is same ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

select * from [Database1].dbo.Table1 where Value not in(select Value from [DataBase2].dbo.Table1)
//Here:
DB1,DB2-Database
same table name present Table1, in both Database.
and Value is the column Name.

Posted 16-Feb-15 23:37pm

Dev Chandra Thakur

Add a Solution

Comments

John C Rayan 17-Feb-15 6:19am

Can you explain better. I am not able to follow you.

Dev Chandra Thakur 17-Feb-15 6:36am

I was trying to compare a column value from two different database...

John C Rayan 17-Feb-15 6:59am

does it have any id column?

John C Rayan 17-Feb-15 7:19am

Check the solution below. This gives the rows that are different in values. If you have any id column then we could use OVER ORDER BY to achieve the same.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

John C Rayan · Answer 1 · 2015-02-17T01:18:00

SQL

select Value11 as Value1,  Value22 as Value2 , 'different' as status from
(
select tbl1.Value as Value11 , tbl2.Value as Value12
from
[DB1].dbo.Table1 tbl1
left outer join [DB2].dbo.Table1 tbl2
on tbl1.Value = tbl2.Value
)first,
(
select tbl1.Value as Value21 , tbl2.Value as Value22
from
[DB1].dbo.Table1 tbl1
right outer join [DB2].dbo.Table1 tbl2
on tbl1.Value = tbl2.Value
) second

where first.Value12 is null and second.Value21 is null

Umer Akram · Answer 2 · 2015-02-17T03:03:00

If you are using SQL SERVER 2005 and above then you can use EXCEPT for data comparison:

Consider the following code:

SQL

Declare @tblA table ( ID int, Name varchar(20))
Declare @tblA_Copy table ( ID int, Name varchar(20))

insert into @tblA
select 1, 'ABC' union all
select 2, 'ABC1' union all
select 3, 'ABC2' union all
select 4, 'ABC3' 

insert into @tblA_Copy
select 11, 'ABC' union all
select 12, 'ABC13' union all
select 13, 'ABC23' union all
select 14, 'ABC33' 

--- all the columns difference From tblA to tblA_Copy
select * from @tblA
except 
select * from @tblA_Copy
--- all the columns difference From tblA_Copy to tblA
select * from @tblA_Copy
except 
select * from @tblA
--- Single columns difference From tblA to tblA_Copy
select Name from @tblA
except 
select Name from @tblA_Copy
--- Single columns difference From tblA_Copy to tblA
select Name from @tblA_Copy
except 
select Name from @tblA

Hope it helps.

Find the difference from two table in two different database, if Column name is same ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0