SQL Server Bulk Insert如何批量數據導入

小編給大家分享一下SQL Server Bulk Insert如何批量數據導入，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

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表可以不存在 

以上是“SQL Server Bulk Insert如何批量數據導入”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！