How to select data from another sql server

We have a target to run a JOB on server to import data from another SQL server instance.

For example take
Server 1 - Main
Server 2 - From which we have to import.

If you have admin rights on both the servers you can "Link the servers" under "Server Objects>>Linked Servers" Right click on the same and provide all the parameters of server or as an alternate you can run following command.

EXEC sp_addlinkedserver
@server = 'db1',
@srvproduct = 'SQLServer OLEDB Provider',
 @provider = 'SQLOLEDB',
 @datasrc = 'server2',
 @provstr='User Id=sa; Password=abc'

The above command parameters:-
 @server- An identification name of server.
@datasrc- Your server instance name or IP.

Your SQL will look like:-
Select * from db1.dbname.dbo.tablename

If sometimes the server will get changed, the above statement gives you the option to change the user name, password and server at any points of time without effecting the relevant SQL and SP written the basis of same.

Second Method (You can use the same if you does not have admin rights to add Linked server)

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=Server2;User ID=sa;Password=abc'
         ).dbname.dbo.tablename
        

In the above case you have to hard code all the parameters which you have to change in all the SQL and SP if something will get changed in future. So, I prefer to use Linked Server

Share: