1、首先,备份本地表
select * into 表2 from 表1
2、Ad Hoc Distributed Queries的启用与关闭
启用Ad Hoc Distributed Queries:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
3、执行拉取远程数据库生成本地表
select * into 表1 from openrowset( 'SQLOLEDB', 'IP,端口'; 'sa'; '密码',数据库.dbo.表)