SQL Server Bulk Insert批量資料匯入

舟之橋發表於2013-02-17
SQLServer

原文連結：http://www.2cto.com/database/201109/102689.html

SQL Server的Bulk Insert語句可以將本地或遠端的資料檔案批量匯入到資料庫中，速度非常的快。遠端檔案必須共享才行，檔案路徑須使用通用約定（UNC）名稱，即"\\伺服器名或IP\共享名\路徑\檔名"的形式。

 
* 1. 由於Bulk Insert通常配合格式化檔案批量匯入資料更方便，所以這裡先介紹bcp工具匯出格式化檔案的方法。
 bcp是SQL Server提供的命令列實用工具提供了資料的匯出、匯入、格式檔案匯出等功能，匯出格式化檔案的語法如下：
Sql程式碼 
bcp 資料庫名.使用者名稱.表名 format nul -- 這裡的nul必須存在，用於不是匯出和匯入資料的情況下 
-f 輸出的格式化檔名 [-x] -c  -- -x引數指定輸出的格式檔案為xml格式（預設非xml格式）; -c引數指定資料儲存方式為字元，並預設指定'\t'作為欄位間隔符;'\n'作為行間隔符 
[-t 欄位間隔符] [-r 行間隔符號]  -- -t與-r引數可選，用於覆蓋-c指定的預設間隔符 
-T -- 指定資料庫連線可信，即使用Windows身份登入 
  
* 2. Bulk Insert
 根據格式檔案匯入資料檔案，語法格式如下：
Sql程式碼 
Bulk insert 資料庫名.使用者名稱.表名 
from '資料檔案路徑' 
with 
( 
formatfile = '格式檔案路徑', 
FirstRow = 2    --指定資料檔案中開始的行數，預設是1 
) 
 
* 3. OPENRORWSET(BULK)函式
 有時，使用OPENROWSET(BULK)函式可以更靈活地選取想要的欄位插入到原表或者其他表中，其語法格式為：
Sql程式碼 
INSERT INTO to_table_name SELECT filed_name_list 
FROM OPENROWSET(BULK N'path_to_data_file', FORMATFILE=N'path_to_format_file') AS new_table_name 
 
  當然，該函式也可以這麼使用：
Sql程式碼 
SELECT field_name_list INTO temp_table_name 
FROM OPENROWSET(BULK N'path_to_data_file', FORMATFILE=N'path_to_format_file') AS new_table_name 
 
 
下面舉一個完整的例子：
 
 1）建立資料庫、表並填充測試資料，指令碼如下：
Sql程式碼 
-- 建立資料庫 
CREATE DATABASE [db_mgr] 
GO 
--建立測試表 
USE db_mgr 
CREATE TABLE dbo.T_Student( 
    F_ID [int] IDENTITY(1,1) NOT NULL, 
    F_Code varchar(10) , 
    F_Name varchar(100) , 
    F_Memo nvarchar(500) , 
    F_Memo2 ntext , 
    PRIMARY KEY  (F_ID) 
)  
GO 
 
--填充測試資料 
Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select 
'code001', 'name001', 'memo001', '備註001' union all select 
'code002', 'name002', 'memo002', '備註002' union all select 
'code003', 'name003', 'memo003', '備註003' union all select 
'code004', 'name004', 'memo004', '備註004' union all select 
'code005', 'name005', 'memo005', '備註005' union all select 
'code006', 'name006', 'memo006', '備註006' 
  2）我們可以使用SQL Server的master..xp_cmdshell儲存過程將CMD的命令傳給系統，這樣就可以直接在SQL Server的查詢處理器中直接輸入bcp的命令，而不用切換到命令模式下執行。SQL Server 出於安全目的預設將該儲存過程禁用了，開啟方法如下：
Sql程式碼 
--開啟xp_cmdshell儲存過程（開啟後有安全隱患） 
EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1; 
EXEC sp_configure 'show advanced options', 0; 
RECONFIGURE; 
  
 3）使用bcp匯出格式檔案：
Sql程式碼 
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T' 
  
 4）使用bcp匯出資料檔案：
Sql程式碼 
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T' 
truncate table db_mgr.dbo.T_Student -- 將表中資料清空 
  注意：在實際使用過程中，資料檔案可以由程式生成，如日誌記錄等！
 
 5）使用Bulk Insert語句批量匯入資料檔案：
Sql程式碼 
BULK INSERT db_mgr.dbo.T_Student 
FROM 'C:/student.data' 
WITH 
( 
    FORMATFILE = 'C:/student_fmt.xml' 
) 
 
 6）使用OPENROWSET(BULK)的例子：
Sql程式碼 
INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name 
FROM OPENROWSET(BULK N'C:/student_c.data', FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name -- T_Student表必須已存在 
 
SELECT F_Code, F_Name INTO db_mgr.dbo.tt 
FROM OPENROWSET(BULK N'C:/student_c.data', FORMATFILE=N'C:/student_fmt_c.xml') AS new_table_name -- tt表可以不存在

相關文章