SqlServer大量匯入

標籤：

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

 

* 1. 由於Bulk Insert通常配合格式檔案大量匯入資料更方便，所以這裡先介紹bcp工具匯出格式化檔案的方法。

bcp是SQL Server提供的命令列工具 + 生產力提供了資料的匯出、匯入、格式檔案匯出等功能，匯出格式化檔案的文法如下：

Sql代碼  

1. bcp 資料庫名.使用者名稱.表名 format nul -- 這裡的nul必須存在，用於不是匯出和匯入資料的情況下  
2. -f 輸出的格式檔案名 [-x] -c  -- -x參數指定輸出的格式檔案為xml格式（預設非xml格式）; -c參數指定資料存放區方式為字元，並預設指定‘\t‘作為欄位間隔符;‘\n‘作為行間隔符  
3. [-t 欄位間隔符] [-r 行間隔符號]  -- -t與-r參數可選，用於覆蓋-c指定的預設間隔符  
4. -T -- 指定資料庫連接可信，即使用Windows身份登入  

* 2. Bulk Insert

 根據格式檔案匯入資料檔案，文法格式如下：

Sql代碼  

1. Bulk insert 資料庫名.使用者名稱.表名  
2. from ‘資料檔案路徑‘  
3. with  
4. (  
5. formatfile = ‘格式檔案路徑‘,  
6. FirstRow = 2    --指定資料檔案中開始的行數，預設是1  
7. )  

 

* 3. OPENRORWSET(BULK)函數

有時，使用OPENROWSET(BULK)函數可以更靈活地選取想要的欄位插入到原表或者其他表中，其文法格式為：

Sql代碼  

1. INSERT INTO to_table_name SELECT filed_name_list  
2. FROM OPENROWSET(BULK N‘path_to_data_file‘, FORMATFILE=N‘path_to_format_file‘) AS new_table_name  

  

  當然，該函數也可以這麼使用：

Sql代碼  

1. SELECT field_name_list INTO temp_table_name  
2. FROM OPENROWSET(BULK N‘path_to_data_file‘, FORMATFILE=N‘path_to_format_file‘) AS new_table_name  

 

 

下面舉一個完整的例子：

 

1）建立資料庫、表並填充測試資料，指令碼如下：

Sql代碼  

1. -- 建立資料庫  
2. CREATE DATABASE [db_mgr]  
3. GO  
4. --建立測試表  
5. USE db_mgr  
6. CREATE TABLE dbo.T_Student(  
7. F_ID [int] IDENTITY(1,1) NOT NULL,  
8. F_Code varchar(10) ,  
9. F_Name varchar(100) ,  
10. F_Memo nvarchar(500) ,  
11. F_Memo2 ntext ,  
12. PRIMARY KEY  (F_ID)  
13. )   
14. GO  
16. --填充測試資料  
17. Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select  
18. ‘code001‘, ‘name001‘, ‘memo001‘, ‘備忘001‘ union all select  
19. ‘code002‘, ‘name002‘, ‘memo002‘, ‘備忘002‘ union all select  
20. ‘code003‘, ‘name003‘, ‘memo003‘, ‘備忘003‘ union all select  
21. ‘code004‘, ‘name004‘, ‘memo004‘, ‘備忘004‘ union all select  
22. ‘code005‘, ‘name005‘, ‘memo005‘, ‘備忘005‘ union all select  
23. ‘code006‘, ‘name006‘, ‘memo006‘, ‘備忘006‘  

  2）我們可以使用SQL Server的master..xp_cmdshell預存程序將CMD的命令傳給系統，這樣就可以直接在SQL Server的查詢處理器中直接輸入bcp的命令，而不用切換到命令模式下執行。SQL Server 出於安全目的預設將該預存程序禁用了，開啟方法如下：

Sql代碼  

1. --開啟xp_cmdshell預存程序（開啟後有安全隱患）  
2. EXEC sp_configure ‘show advanced options‘, 1;  
3. RECONFIGURE;EXEC sp_configure ‘xp_cmdshell‘, 1;  
4. EXEC sp_configure ‘show advanced options‘, 0;  
5. RECONFIGURE;  

  

3）使用bcp匯出格式檔案：

Sql代碼  

1. EXEC master..xp_cmdshell ‘BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T‘  

  

4）使用bcp匯出資料檔案：

Sql代碼  

1. EXEC master..xp_cmdshell ‘BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T‘  
2. truncate table db_mgr.dbo.T_Student -- 將表中資料清空  

  注意：在實際使用過程中，資料檔案可以由程式產生，如日誌記錄等！

 

5）使用Bulk Insert語句大量匯入資料檔案：

Sql代碼  

1. BULK INSERT db_mgr.dbo.T_Student  
2. FROM ‘C:/student.data‘  
3. WITH  
4. (  
5. FORMATFILE = ‘C:/student_fmt.xml‘  
6. )  

 

6）使用OPENROWSET(BULK)的例子：

Sql代碼  

1. INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name  
2. FROM OPENROWSET(BULK N‘C:/student_c.data‘, FORMATFILE=N‘C:/student_fmt_c.xml‘) AS new_table_name -- T_Student表必須已存在  
4. SELECT F_Code, F_Name INTO db_mgr.dbo.tt  
5. FROM OPENROWSET(BULK N‘C:/student_c.data‘, FORMATFILE=N‘C:/student_fmt_c.xml‘) AS new_table_name -- tt表可以不存在  

 

 

參考：

使用 BULK INSERT 或 OPENROWSET(BULK...) 匯入大容量資料（尤其是關於安全的那部分，匯入遠程檔案時應特別注意）：

http://msdn.microsoft.com/zh-cn/library/ms175915.aspx

建立格式檔案：

http://msdn.microsoft.com/zh-cn/library/ms191516.aspx

OPENROWSET (Transact-SQL)：

http://msdn.microsoft.com/zh-cn/library/ms190312.aspx

BULK INSERT (Transact-SQL)：

http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

bcp 工具 + 生產力：

http://msdn.microsoft.com/zh-cn/library/ms162802.aspx

SqlServer大量匯入