SQL Server高速匯入資料分享

標籤：select   rom   out   2.0   .com   引用   color   tran   rar   

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 不使用連結的server名，而提供特殊的串連資訊，並將其作為四部分對象名的一部分。 而OpenRowSet 包括訪問 OLE DB 資料來源中的遠端資料所需的所有串連資訊。

當訪問連結server中的表時，這樣的方法是一種替代方法，而且是一種使用 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!

SQL Server高速匯入資料分享