3

i have an idea to call two values from two different databases and comapre them in one statement? is it possible? i am working with c# and MS-SQL

kevchadders
  • 8,335
  • 4
  • 42
  • 61
r.r
  • 7,023
  • 28
  • 87
  • 129
  • possible duplicate of [Selecting data from two different servers in SQL Server](http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server) –  Mar 09 '11 at 13:27
  • 1
    This is not a duplicate. He is asking about querying two different databases, not specifically asking about querying from two different servers. For two different databases simply add the database name to the front of your table (fully qualify it in other words) as in select * from master.dbo.systables, to query data from another server you would need to use linked servers. – Shane Delmore Mar 09 '11 at 14:41

4 Answers4

6

Yes.

For MSSQL you can add the database name in front of your table. You normally have 4 namespaces you can use

[Server name].[database name].[owner].[table_name]

So if you want to compare two values in the one statement you should only need to join across the tables by placing the database name in front of the table name.

If the databases are on different servers then you will need to create a linked server to the side which will run your SQL so that its aware of the other sql server. You can add linked servers in Management studio or via SQL using something like sp_addlinkedserver

kevchadders
  • 8,335
  • 4
  • 42
  • 61
5

You may do cross database joins to compare these values:

SELECT
   db1.Value as value1,
   db2.Value as value2
FROM
   [database1].dbo.MyTable1 as db1
   INNER JOIN
   [database2].dbo.MyTable as db2
    ON   
    /* insert join clasue */
Torbjörn Hansson
  • 18,354
  • 5
  • 33
  • 42
0

There are a few possibilities here depending on your setup. If your databases are different SQL Server installations then you will want to look at sp_linkedserver first. Once you have the ability to see both databases using the same login you could just execute the following query where db1 and db2 are the databases, dbo is the owner and tab1 and tab2 are the respective tables.

SELECT a.col1 
FROM db1.dbo.tab1 a, db2.dbo.tab2 b
WHERE a.col1 = b.col2
Community
  • 1
  • 1
scottrudy
  • 1,633
  • 1
  • 14
  • 24
0

If you should happen to lack the SQL Server permissions to create a linked server, you could create connections to each server and your client could attach to the servers using Microsoft JET library, and then you could perform the heterogeneous join client-side.

Tim
  • 5,371
  • 3
  • 32
  • 41