Querying data by joining two tables in two database on different servers

by

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.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Insert an image in a column of a table that already has 5 columns
  3. How can I schedule a job to run a SQL query daily?
  4. Reset identity seed after deleting records in SQL Server
  5. How to avoid the “divide by zero” error in SQL?
  6. SET versus SELECT when assigning variables?
  7. Generate a resultset of incrementing dates in TSQL
  8. sql query to return differences between two tables
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. How to print VARCHAR(MAX) using Print Statement?
  11. Concatenate values based on ID
  12. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  13. Splitting delimited values in a SQL column into multiple rows
  14. How to find current transaction level?
  15. EXISTS vs JOIN and use of EXISTS clause
  16. Restrict an SQL Server connection to a specific IP address
  17. How to check which locks are held on a table
  18. Cumulative Total in MS Sql server [duplicate]
  19. How do I change db schema to dbo
  20. How do I find the data directory for a SQL Server instance?
  21. Single or multiple databases
  22. How to update two tables in one statement in SQL Server 2005?
  23. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  24. Is the NOLOCK (Sql Server hint) bad practice?
  25. How to find all connected subgraphs of an undirected graph
  26. SQL query to split column data into rows
  27. Using PIVOT in SQL Server 2008
  28. Function in SQL Server 2008 similar to GREATEST in mysql?
  29. Query times out from web app but runs fine from management studio
  30. SQL . The SP or function should calculate the next date for friday

Leave a Comment