SQL Server快速匯入資料分享

SQL Server快速匯入資料，可以嘗試的方法如下：CTE、OpenRowSet/OpenDataSource、BULK INSERT、bcp、Shell。

下面依次介紹這幾種辦法。

1.CTE首先，我們看看什麼是CTE。通用資料表運算式（Common Table Expression)是SQL SERVER 2005版本之後引入的一個特性。CTE可以看作是一個臨時的結果集，可以在接下來的一個SELECT，INSERT，UPDATE，DELETE，MERGE語句中被多次引用。使用公用運算式可以讓語句更加清晰簡練。CTE 與派生表類似，具體表現在不儲存為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。
更多請點擊：http://technet.microsoft.com/zh-cn/library/ms190766(v=sql.105).aspx


樣本如下：

USE AdventureWorks2008R2;GO-- Define the CTE expression name and column list.WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)AS-- Define the CTE query.(    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear    INTO #temp1    FROM Sales.SalesOrderHeader    WHERE SalesPersonID IS NOT NULL)-- Define the outer query referencing the CTE name.SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYearINTO #temp2FROM Sales_CTEGROUP BY SalesYear, SalesPersonIDORDER BY SalesPersonID, SalesYear;GO

2.OpenRowSet/OpenDataSourceOpenRowSet和OpenDataSource都可以訪問遠端資料庫，但具體表現上，二者還是有差別的。OpenDataSource 不使用連結的伺服器名，而提供特殊的串連資訊，並將其作為四部分對象名的一部分。 而OpenRowSet 包含訪問 OLE DB 資料來源中的遠端資料所需的全部串連資訊。當訪問連結的伺服器中的表時，這種方法是一種替代方法，並且是一種使用 OLE DB 串連並訪問遠端資料的一次性的、特殊的方法。可以在查詢的 FROM 子句中像參考資料表那樣引用 OpenRowSet 函數。依據 OLE DB 提供者的能力，還可以將 而OpenRowSet 函數引用為 INSERT、UPDATE 或 DELETE 語句的目標表。儘管查詢可能返回多個結果集，然而OPENROWSET 只返回第一個。更多請點擊：http://technet.microsoft.com/en-us/library/ms179856.aspx


樣本如下：

--啟用Ad Hoc Distributed QueriesEXEC SP_CONFIGURE 'show advanced options',1RECONFIGUREEXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',1RECONFIGURE--使用OpenDataSource匯入資料INSERT INTO IMP_DATA.dbo.t_goodsSELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.12.0', 'Data Source="E:/Report1.txt";User ID=Admin;Password=;Extended properties=Excel 12.0')...[Sheet1$]--使用完畢後，切記關閉它，因為這是一個安全隱患EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',0RECONFIGUREEXEC SP_CONFIGURE  'show advanced options',0RECONFIGURE 

3.BULK INSERT

BULK INSERT允許使用者以其指定的格式將資料檔案匯入到資料庫表或視圖中。更多請點擊：http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

樣本如下：

--定義匯入目的和匯入源BULK INSERT IMP_DATA.dbo.t_goods FROM 'E:/Report1.txt' WITH (   --資料行分隔符號  FIELDTERMINATOR = ',',  --行分隔字元  ROWTERMINATOR = '\n'  )

4.bcpbcp 工具 + 生產力可以在 Microsoft SQL Server 執行個體和使用者指定格式的資料檔案間大量複製資料。 使用 bcp 工具 + 生產力可以將大量新行匯入 SQL Server 表，或將表資料匯出到資料檔案。 除非與 queryout 選項一起使用，否則使用該工具 + 生產力不需要瞭解 Transact-SQL 知識。 若要將資料匯入表中，必須使用為該表建立的格式檔案，或者必須瞭解表的結構以及對於該表中的列有效資料類型。
更多請點擊：http://msdn.microsoft.com/zh-cn/library/ms162802.aspx


樣本如下：

--開啟進階選項EXEC SP_CONFIGURE 'show advanced options', 1;RECONFIGURE;--啟用執行CMD命令EXEC SP_CONFIGURE 'xp_cmdshell', 1;RECONFIGURE;--指定匯入目的和匯入源EXEC master..xp_cmdshell 'BCP IMP_DATA.dbo.t_goods in E:\report.txt -c -T'

5.Shell
Shell通過拼接插入字串的方法非常靈活，並且出錯較少，但插入的內容包含很多非法字元的話會很惱。可以參考以前寫的文章：缺乏匯入資料許可權，SQL Server建立測試資料

最後，貼張前段時間做的圖，匯入資料總結：

Good Luck!