SQL Server Bulk Insert如何批量數(shù)據(jù)導(dǎo)入

135次閱讀

共計 2778 個字符，預(yù)計需要花費 7 分鐘才能閱讀完成。

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

SQL Server 的 Bulk Insert 語句可以將本地或遠程的數(shù)據(jù)文件批量導(dǎo)入到中，速度非常的快。遠程文件必須共享才行，文件路徑須使用通用約定（UNC）名稱，即 \\ 服務(wù)器名或 IP\ 共享名 \ 路徑 \ 文件名的形式。

* 1. 由于 Bulk Insert 通常配合格式化文件批量導(dǎo)入數(shù)據(jù)更方便，所以這里先介紹 bcp 工具導(dǎo)出格式化文件的方法。
bcp 是 SQL Server 提供的命令行實用工具提供了數(shù)據(jù)的導(dǎo)出、導(dǎo)入、格式文件導(dǎo)出等功能，導(dǎo)出格式化文件的語法如下：
Sql 代碼
bcp 數(shù)據(jù)庫名. 用戶名. 表名 format nul — 這里的 nul 必須存在，用于不是導(dǎo)出和導(dǎo)入數(shù)據(jù)的情況下
-f 輸出的格式化文件名 [-x] -c — - x 參數(shù)指定輸出的格式文件為 xml 格式（默認非 xml 格式）; - c 參數(shù)指定數(shù)據(jù)存儲方式為字符，并默認指定 \t 作為字段間隔符; \n 作為行間隔符
[-t 字段間隔符] [-r 行間隔符號] — - t 與 - r 參數(shù)可選，用于覆蓋 - c 指定的默認間隔符
-T — 指定數(shù)據(jù)庫連接可信，即使用 Windows 身份登錄

* 2. Bulk Insert
根據(jù)格式文件導(dǎo)入數(shù)據(jù)文件，語法格式如下：
Sql 代碼
Bulk insert 數(shù)據(jù)庫名. 用戶名. 表名
from 數(shù)據(jù)文件路徑
with
(
formatfile = 格式文件路徑 ,
FirstRow = 2 – 指定數(shù)據(jù)文件中開始的行數(shù)，默認是 1
)

* 3. OPENRORWSET(BULK) 函數(shù)
有時，使用 OPENROWSET(BULK) 函數(shù)可以更靈活地選取想要的字段插入到原表或者其他表中，其語法格式為：
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

當然，該函數(shù)也可以這么使用：
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）創(chuàng)建數(shù)據(jù)庫、表并填充測試數(shù)據(jù)，腳本如下：
Sql 代碼
— 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE [db_mgr]
GO
– 創(chuàng)建測試表
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

– 填充測試數(shù)據(jù)
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 的命令傳給系統(tǒng)，這樣就可以直接在 SQL Server 的查詢處理器中直接輸入 bcp 的命令，而不用切換到命令模式下執(zhí)行。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 導(dǎo)出格式文件：
Sql 代碼
EXEC master..xp_cmdshell BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T

4）使用 bcp 導(dǎo)出數(shù)據(jù)文件：
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 — 將表中數(shù)據(jù)清空
注意：在實際使用過程中，數(shù)據(jù)文件可以由程序生成，如日志記錄等！

5）使用 Bulk Insert 語句批量導(dǎo)入數(shù)據(jù)文件：
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 如何批量數(shù)據(jù)導(dǎo)入”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完