不服务器之间复制
先打开Ad Hoc服务(否则报错)
EXEC sp_configure "show advanced options",1
RECONFIGURE
EXEC sp_configure "Ad Hoc Distributed Queries",1
RECONFIGURE
连接操作
SELECT * INTO DatabaseB.tableB FROM opendatasource( 'SQLOLEDB', 'Data Source=192.168.0.110;User ID=SA;Password=*** ).Databasea.dbo.tableA WHERE 1=2(where 1=2 只复制表结构,不加的话 数据一起复制)
关闭Ad Hoc服务
EXEC sp_configure "Ad Hoc Distributed Queries",0
RECONFIGURE
EXEC sp_configure "show advanced options",0
RECONFIGURE
同一服务器
1、复制表结构和数据:SELECT * INTO DatabaseB.dbo.TableB FROM DatabaseA.dbo.TableA
2、仅仅复制表结构:SELECT * INTO DatabaseB.dbo.TableB FROM DatabaseA.dbo.TableA WHERE 1=2