How to select data of a table from another database in SQL Server?

More details: Navigating Diverse Databases

Operating within multiple databases on a single server or journeying across servers, SQL Server extends several approaches to navigate these tasks effectively.

Cross-server query with Linked Servers

Assuming the databases are residing on diverse servers, sp_addlinkedserver is enlisted to set up linked servers. You can now run the queries as if you're working on your local server, just like a universal remote to the server universe.

EXEC sp_addlinkedserver 'TheNeighbourServer';  -- We're all friends here


Post establishing the linked server, your SQL query turns into an international passport:

SELECT * FROM [AloofServer].[DatabaseInUse].[RelevantSchema].[TargetTable];


For instance, accessing "testdb" on the "test server" from your current "proddb" on the "prod server":

SELECT * FROM [testserver].[testdb].[dbo].[Employee]; -- Nice weather at testserver today!


Direct database queries in SQL Server 2012+

In SQL Server 2012 and beyond, direct querying to a different database, all on the same server, is as simple as pie. The three-part naming serves the need just right, it's a no-brainer even without initiating linked servers:

SELECT * FROM [TheDatabaseOnCall].dbo.[MarathonTable];


The Security Corner: Cross-database Considerations

When working with cross-database or cross-server inquiries, waving the security flag becomes elemental to maintain data confidentiality and integrity. Involve appropriate permissions and also mind the ownership chaining for secure chit-chat with the data environment.

Scaling up: Querying from a linked server

On few occasions, the requirement so steps up that linking to a remote server becomes inevitable. Fear not, your query structure is just a bit modified, not rocket science.

Linked server setup:

First off, grab the credentials, then set up the linked server:

EXEC sp_addlinkedserver   
   @server=N'ServerNickname',   
   @srvproduct=N'',
   @provider=N'SQLNCLI',   
   @datasrc=N'TheServerAddress';  -- We're going on an adventure!


Query execution:

Post successful completion, summon the tables from the remote server:

SELECT * FROM [ServerNickname].[databaseWhereabouts].[schemaLocation].[tableAddress]


Ensure to replace all placeholders with the actuals — ServerNickname, databaseWhereabouts, schemaLocation, and tableAddress, they're waiting for you!

Quid pro quo — Synonyms for quicker queries

In SQL Server, synonyms offer the ease of referring to often-used tables from disparate databases or servers. It's like a nickname for lengthy table addresses:

CREATE SYNONYM MyEmployee FOR [DistantServer].[HR].[dbo].[Employee];  -- Say hello to 'MyEmployee'


Now index it further simply by:

SELECT * FROM MyEmployee;  -- 'MyEmployee' at your service!
