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
-
possible duplicate of Selecting data from two different servers in SQL Serveruser57508– user575082011-03-09 13:27:37 +00:00Commented Mar 9, 2011 at 13:27
-
1This 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– Shane Delmore2011-03-09 14:41:33 +00:00Commented Mar 9, 2011 at 14:41
Add a comment
|
4 Answers
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
1 Comment
Filip De Vos
in sql server 2005 and higher the [owner] part has changed to [schema]
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 */
Comments
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
Comments
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.
