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

Question

Hi,

Suppose, I have a database named testdb in test server. Also I 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?

please help.

Thanks

Baju

Answer 1

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.

Answer 2

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]