Source: http://www.cnblogs.com/doosmile/archive/2012/03/16/2400646.html
--Connect remote SQL or insert data with OPENROWSET
--if it's just temporary access, you can use OPENROWSET directly
--query Example
SELECT * FROM OPENROWSET (' SQLOLEDB '
, ' SQL Server name '; ' User name '; ' Password
, database name. dbo. Table name)
You need to enable AD HOC distributed for cross-Library queries Queries
Enable AD Hoc distributed Queries:
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
When you are finished using, close ad Hoc distributed Queries:
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure
--import Example
SELECT * Into table from OPENROWSET (' SQLOLEDB '
, ' SQL Server name '; ' User name '; ' Password
, database name. dbo. Table name)
--Create a linked server
exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' username ', ' password '
Go
--query Example
SELECT * from Srv_lnk. Database name. dbo. Table name
--import Example
SELECT * into table from Srv_lnk. Database name. dbo. Table name
--Remove linked server when no longer in use
exec sp_dropserver ' srv_lnk ', ' droplogins '
Go
--The following example accesses data from a table that is in another instance of SQL Server.
SELECT *from opendatasource (' SQLOLEDB ', ' Data source=servername; User Id=myuid; Password=mypass '). Northwind.dbo.Categories
--Here is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet.
Select *from opendatasource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:financeaccount.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions
SQL Server cross-Library query--distributed query