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

Question

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?

If you're using SQL Server 2012 or above, jump down to https://stackoverflow.com/a/41127437/733092. — Noumenon, Jun 12 '18 at 16:29
This same topic has been discussed also in https://stackoverflow.com/q/3788566/1548275 — Jari Turkia, Aug 17 '23 at 11:39

score 67 · Answer 1 · answered Apr 06 '09 at 08:30

67

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]

answered Apr 06 '09 at 08:30

Matthew Farwell

60,889
18
128
171

5

If you need to pass through different credentials, use EXEC sp_addlinkedsrvlogin 'servername', 'false', NULL, 'SqlUser', 'password' – Peter Munnings May 29 '12 at 10:59
I was struggling to get this to work, found you can also do this using SSMS (https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/) 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. – Craig Hughes Mar 09 '18 at 10:59

score 54 · Answer 2 · answered Dec 13 '16 at 17:45

54

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

answered Dec 13 '16 at 17:45

Arthur Ronald

33,349
20
110
136

1

Doesn't work on Azure. If you need it in Azure, Try this: https://stackoverflow.com/questions/45715668 – MGOwen Mar 11 '19 at 01:07
This works in Azure now with the same user in both databases – Ryan Apr 01 '19 at 21:28

score 12 · Answer 3 · answered May 04 '11 at 14:13

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]

Ihor Konovalenko · Answer 4 · 2020-06-13T14:31:51.730

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]

Mitch Baker · Answer 5 · 2009-04-06T05:34:35.393

5

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.

edited Apr 06 '09 at 05:34

answered Apr 06 '09 at 05:28

Mitch Baker

624
3
7

The tables are in different server. – user82431 Apr 06 '09 at 05:30
How can I link between to servers and how can I select data? please provide steps. thanks – user82431 Apr 06 '09 at 05:34

score 1 · Answer 6 · answered Nov 26 '19 at 11:46

Try using OPENDATASOURCE The syntax is like this:

select * from OPENDATASOURCE ('SQLNCLI', 'Data Source=192.168.6.69;Initial Catalog=AnotherDatabase;Persist Security Info=True;User ID=sa;Password=AnotherDBPassword;MultipleActiveResultSets=true;' ).HumanResources.Department.MyTable

score 1 · Answer 7 · answered Aug 10 '21 at 11:16

1

Select * from [Database].[dbo].[TableName]
select * from [dbTest].[dbo].[Products]

answered Aug 10 '21 at 11:16

Arshman Saleem

135
1
7

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

7 Answers7

Linked

Related