CODEWITHSUNDEEP
sqlsql-server-2008
user1100199
user1100199

Reputation: 53

mapping of data between same server but diffrent databases with same table names

Hi I am stuck here need your advice. I have a server with multiple DB's. Now I want to map if the data in one table of 1 db is equal to the data in another db with same table name

can anyone suggest how to do that?? thanks in advancve

Upvotes: 1

Views: 487

Answers (2)

Diego
Diego

Reputation: 36146

depends on what you need to map. If you just want to know the differences by primary key, I would try a full join on the PK, so it will tell you records that exist on A but not on B and records that exists on B but not on A. Like this:

create table DB_A(id int)
create table DB_B(id int)

insert into DB_A values (1)
insert into DB_A values (2)

insert into DB_B values (2)
insert into DB_B values (3)

select DB_A.ID as 'Exists on A but not on B', DB_B.id as  'Exists on B but not on A'
from DB_A full join DB_B on DB_A.id=DB_B.id 
where DB_A.id is null or DB_B.id is null

if you need more than that like compare the values of all columns, I suggest you to use a data compare tool. It would not be so strait forward to do it using just SQL

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select * from db1.table1 t1
full outer join db2.table2 t2 on t1.id = t2.id
where t1.id <> t2.id
or t1.col2 <> t2.col2 or ...

Upvotes: 1

Related Questions