SQL Server快速匯入資料分享

Wentasy發表於2014-04-26
SQLServer
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, SalesYear
INTO #temp2
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO


2.OpenRowSet/OpenDataSource

OpenRowSet和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 Queries
EXEC SP_CONFIGURE 'show advanced options',1
RECONFIGURE
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE
--使用OpenDataSource匯入資料
INSERT INTO IMP_DATA.dbo.t_goods
SELECT * 
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',0
RECONFIGURE
EXEC SP_CONFIGURE  'show advanced options',0
RECONFIGURE


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.bcp

bcp 實用工具可以在 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!


相關文章