-1

Is it possible to write a query which uses tables which are in different databases on different servers?

I have table_1 which exists in database "db1" on server1 and there is another table table_2 which exists in database "db2" on server2.

How can I do this? The servers can be any of MySQL, HSQL or MS-SQL.This is the main question that.........there are not different server but also different type of servers.

Will sp_addlinkedserver work for such case???

caesar
  • 11
  • 1
  • 5
  • 2
    Possible duplicate of [Selecting data from two different servers in SQL Server](https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server) – Tab Alleman Aug 30 '17 at 13:55

3 Answers3

0

Set up linked servers on the MS SQL server. Then you can just query both servers like

select * from [server1].[database].[dbo].[table]

and

select * from [server2].[database]..[table]

or use OPENQUERY (might be preferred cause this just sends the command to the other server rather than the source server trying to parse it)

Element Zero
  • 1,651
  • 3
  • 13
  • 31
  • will this also work when both servers are of different type?eg- one is of hsql and other of MySQL? – caesar Aug 30 '17 at 14:22
  • It will work yes (ESPECIALLY if you use OPENQUERY) however in order to create the linked server you may need to install the drivers for the other server types onto the SQL server. Here is a link on how to do this for MySQL. https://www.mssqltips.com/sqlservertip/4577/create-a-linked-server-to-mysql-from-sql-server/ – Element Zero Aug 30 '17 at 14:24
0

You'll need to use sp_addlinkedserver to create a server link. See the reference documentation for usage. Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server. I.E:

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

Once the link is established, you can also use OPENQUERY to execute a SQL statement on the remote server and transfer only the data back to you. This can be a bit faster, and it will let the remote server optimize your query. If you cache the data in a temporary (or in-memory) table on DB1 in the example above, then you'll be able to query it just like joining a standard table. For example:

-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID

Check out the documentation for OPENQUERY to see some more examples. The example above is pretty contrived. I would definitely use the first method in this specific example, but the second option using OPENQUERY can save some time and performance if you use the query to filter out some data.

Manvendra Rajpurohit
  • 307
  • 5
  • 17
0

The following Query will helpful.

SELECT TS1.COLUMN01, TS2.COLUMN02 
FROM Server1.DBName..TableName TS1, Server2.DBName..TableName TS2 
WHERE TS1.COLUMN03 = TS2.COLUMN03
Shyam Vemula
  • 591
  • 2
  • 14