SQL Server 跨庫複製表資料的解決辦法

跨庫複製表資料，有很多種方法，最常見的是寫程式來大量匯入資料了，但是這種方法並不是最優方法，今天就用到了一個很犀利的方法，可以完美在 Sql Server 2005 和 Sql Server 2008 中執行！

格式如下：

insert into tableA  SELECT * FROM          OPENDATASOURCE('SQLOLEDB', 'Data Source=127.0.0.1;User ID=sa;Password=sasasa').databaseName.dbo.tableB  

找到這個方法後，準備執行，可是卻並不太順利，跨庫複製表資料的途中，接連出現兩個錯誤，第一個錯誤：

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

翻譯：

SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的
STATEMENT'OpenRowset/OpenDatasource'
的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc
Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細資料，請參閱 SQL
Server 聯機叢書中的 "介面區配置器"。

 

解決辦法：

 

啟用  Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure

待插入完成後再關閉 Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0reconfigureexec sp_configure 'show advanced options',0reconfigure

錯誤2 ：

An explicit value for the identity column in table 'cms_TagSubject' can only be specified when a column list is used and IDENTITY_INSERT is ON.

這個真的很糾結，沒辦法，只有 google 了，之後發現可以 在執行的 SQL 陳述式前後加上：

SET IDENTITY_INSERT tableA ON--執行的SQLSET IDENTITY_INSERT tableB ON

試過之後，發現這個方法並不能解決，無奈，最後 找了半天，在國外論壇找到瞭解決辦法，就是，要寫查入列的詳細資料

 

解決辦法：

insert into tableA (column1,column2.....) 
SELECT * FROM          
OPENDATASOURCE('SQLOLEDB', 'Data Source=127.0.0.1,3422;User ID=sa;Password=sasasa;').databaseName.dbo.tableB  

終於大功告成，另外，利用這種方法，還是可以直接從 Excel 裡面查詢的，呵呵，真強大：

SELECT *  FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\a.xls',[sheet1$])

跨庫複製表資料,一種很好的方法，呵呵，希望能對大家有所協助