CODEWITHSUNDEEP
sqlsql-serverdatabaselinked-server
user82431
user82431

Reputation: 1721

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

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?

Upvotes: 76

Views: 507864

Answers (7)

Arshman Saleem
Arshman Saleem

Reputation: 205

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

Upvotes: 2

Ihor Konovalenko
Ihor Konovalenko

Reputation: 1407

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]

Upvotes: 6

Alireza
Alireza

Reputation: 85

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

Upvotes: 1

Arthur Ronald
Arthur Ronald

Reputation: 33785

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

Upvotes: 55

Brian Wells
Brian Wells

Reputation: 1582

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]

Upvotes: 12

Matthew Farwell
Matthew Farwell

Reputation: 61705

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]

Upvotes: 73

Mitch Baker
Mitch Baker

Reputation: 624

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.

Upvotes: 5

Related Questions