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?

Answer 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] 

Answer 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