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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to linked-server