how to compare two database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to compare two database

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-05 : 12:16:22

i have two database db1 and db2
both databases has same table structurs.
Now i have to update db2's table with comparing db1.

tbl1 has following fileds.
id,fname,lname,email,status

now i have to update db2's tbl1 (both db has tbl1)
if db1's tbl1>> email is same as db2's tbl1>> email
than i have to update db2>>tbl1>>status=1

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-05 : 12:21:24

try this

update y
set y.status = case when x.email = y.email then 1 else 0 end
from db1.tbl1 x
inner join db2.tbl1 y on y.id = x.id