Selecting Data from Two Different Servers in SQL Server

SELECT Query between two different servers

Thanks everyone, I found a solution for my problem.

You can check the solution link here or do below steps.

1. Try to create a linkServer , Object Explorer -> Server Objects -> Linked Servers (Right click and create new)

2. Configure Settings

Enter your Server Name

Login with sa and password

3. Then Test Connection of it.

4. Enjoy your query.

select
lc.t2 as 'Local Stock Code',
ext.t2 as 'External Stock Code'
from
Stk001 lc,
[V5_27\SQL2005].[TESTACC].[dbo].[STK001] ext

Query tables between two different servers in SQL Server

Another way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. To use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources.

Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function.

You need to provide the provider’s name, the connection string and the query as follows:

OPENROWSET(‘providername’,’datascource,’query)

How to execute Union of 2 queries from different SQL Servers?

You need to create the linked server so you have to access of that server after that You can try this way to call and execute the query.

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Select * from <ServerName>.<databaseName>.<SchemaName>.<Table1>

Here is the official documentation to configure the link server.

Getting data from different database on different server with one SQL Server query

Creating a linked server is the only approach that I am aware of for this to occur. If you are simply trying to add all new rows from prod to dev then why not just create a backup of that one particular table and pull it into the dev environment then write the query from the same server and database?

Granted this is a one time use and a pain for re-occuring instances but if it is a one time thing then I would recommend doing that. Otherwise make a linked server between the two.

To backup a single table in SQL use the SQl Server import and export wizard. Select the prod database as your datasource and then select only the prod table as your source table and make a new table in the dev environment for your destination table.

This should get you what you are looking for.

SQL Query across multiple SQL servers

You can proceed with Linked Servers using sp_addlinkedserver.
Once done, you can query your data as you mentioned;

SELECT  *
FROM    [Db1].[dbo].table1 A
INNER JOIN [Server2].[Db1].[dbo].table2 B 
ON A.Id = B.Id

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

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.

---

Related Topics