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.
| Author |
Topic |
|
romeck
Starting Member
16 Posts |
 Posted - 2012-03-14 : 06:59:32
|
| Is it posible to create sql query that will check data from other database on other sql instance ? I have 2 database (db1, db2) on 2 SQL 2008R2 Instancec(in1,in2) i would like to do sth like that : Structures of SQL instalationdb1 has just 1 table tb1[id, nr] , db2 1 table tb1[id,nr]select * from in2.db2.tb1 where in2.db2.tb1.nr not in( select nr from in1.db1.tb1.nrIs it possible ? if it is is there a better way to have data i need from db2 that have no corespondant data in db1 ? ( the query i wrot seems not to optimal )THQ for help |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-14 : 07:27:02
|
You can do this - you will need to link the two servers. See here: http://msdn.microsoft.com/en-us/library/ms188279.aspx Once you have linked, you will use the four-part naming convention to access tables/views on the remote server. (in2.db2.dbo.tb1, for example) http://msdn.microsoft.com/en-us/library/ms177563.aspxLinked server queries would not be as efficient as local queries, obviously - but if the data is on the remote server, you just may have to live with it. If the data is static, you might make a copy and put it on the local server. Or you may want to consider replication.As an aside, depending on your data, and the logic you want to implement, it may be better to use the NOT EXISTS clause rather than NOT IN, as in:select * from in2.db2.tb1 n2 where NOT EXISTS (SELECT * FROM in1.db1.tb1 n1 WHERE n1.nr = n2.nr) |
 |
|
|
|
|
|
|
|
Select that can check data from other database