1、启用Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

2、sql语句

insert into  datatable(id)

select top 100 id from OPENDATASOURCE('SQLOLEDB',

'Data Source = 192.168.10.19;User ID=sa;Password=123456;').database.dbo.table

查excel文件也是同样：

select * from  OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel

8.0;IMEX=1;HDR=YES;DATABASE=D:\test.xls',[sheet1$])

3、使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure 'show advanced options',0

reconfigure