How to select data of a table from another database in SQL Server?
Solution 1
You need sp_addlinkedserver()
http://msdn.microsoft.com/en-us/library/ms190479.aspx
Example:
exec sp_addlinkedserver @server = 'test'
then
select * from [server].[database].[schema].[table]
In your example:
select * from [test].[testdb].[dbo].[table]
Solution 2
In SQL Server 2012 and above, you don't need to create a link. You can execute directly
SELECT * FROM [TARGET_DATABASE].dbo.[TABLE] AS _TARGET
I don't know whether previous versions of SQL Server work as well
Solution 3
I've used this before to setup a query against another server and db via linked server:
EXEC sp_addlinkedserver @server='PWA_ProjectServer', @srvproduct='',
@provider='SQLOLEDB', @datasrc='SERVERNAME\PWA_ProjectServer'
per the comment above:
select * from [server].[database].[schema].[table]
e.g.
select top 6 * from [PWA_ProjectServer].[PWA_ProjectServer_Reporting].[dbo].[MSP_AdminStatus]
Solution 4
To do a cross server query, check out the system stored procedure: sp_addlinkedserver in the help files.
Once the server is linked you can run a query against it.
Solution 5
Using Microsoft SQL Server Management Studio you can create Linked Server
. First make connection to current (local) server, then go to Server Objects
> Linked Servers
> context menu > New Linked Server
. In window New Linked Server
you have to specify desired server name for remote server, real server name or IP address (Data Source) and credentials (Security page).
And further you can select data from linked server:
select * from [linked_server_name].[database].[schema].[table]
user82431
Updated on August 11, 2021Comments
-
user82431 almost 3 years
Suppose that I have a database which name is
testdb
in test server. I also have a database named proddb in prod server.Now I want to select data of a table of testdb database from proddb database.
How can I do that in
SQL Server
?Also, I can do it using database link in oracle. But how can do that in SQL Server?
-
user82431 about 15 yearsThe tables are in different server.
-
Peter Munnings almost 12 yearsIf you need to pass through different credentials, use EXEC sp_addlinkedsrvlogin 'servername', 'false', NULL, 'SqlUser', 'password'
-
Admin about 6 yearsI was struggling to get this to work, found you can also do this using SSMS (sqlshack.com/…) which then shows you all the various security etc options that are available - i found i had to set set it to use the current logins security context to get it to work.
-
MGOwen about 5 yearsDoesn't work on Azure. If you need it in Azure, Try this: stackoverflow.com/questions/45715668
-
Ryan about 5 yearsThis works in Azure now with the same user in both databases