SQL数据导入导出方法总结之BULK INSERT

 和BCP一样，BULK INSERT陈述式要设定几个必要参数和几个选择性使用的参数。 您可以使用以下的指令，在SQL Server 內部呼叫BULK INSERT（使用ISQL、OSQL 或Query Analyzer），其中包含了必要及選擇性使用的參數： 您可以使用以下的指令，在SQL Server内部呼叫BULK INSERT（使用ISQL、OSQL或Query Analyzer），其中包含了必要及选择性使用的参数：

BULK INSERT [[ 'database_name'.]['owner'].]               { 'table_name' FROM 'data_file' }      [WITH (               [BATCHSIZE [ = batch_size ]]              [[ , ] CHECK_CONSTRAINTS ]              [[ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]]              [[ , ] DATAFILETYPE   [ = {          'char' | 'native'|                      'widechar' | 'widenative' }]]              [[ , ] FIELDTERMINATOR [ = 'field_terminator' ]]              [[ , ] FIRSTROW [ =first_row]]              [[ , ] FIRETRIGGERS [ =fire_triggers]]              [[ , ] FORMATFILE = 'format_file_path' ]              [[ , ] KEEPIDENTITY ]              [[ , ] KEEPNULLS ]              [[ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]]              [[ , ] LASTROW [ = last_row ] ]              [[ , ] MAXERRORS [ =max_errors]]              [[ , ] ORDER({column[ ASC | DESC ]}[,...n])]              [[ , ] ROWS_PER_BATCH [ =rows_per_batch]]              [[ , ] ROWTERMINATOR [ = 'row_terminator' ]]              [[ , ] TABLOCK ]            )] BULK INSERT [[ 'database_name'.]['owner'].] { 'table_name' FROM 'data_file' } [WITH ( [BATCHSIZE [ = batch_size ]] [[ , ] CHECK_CONSTRAINTS ] [[ , ] CODEPAGE [ = 'ACP ' | 'OEM' | 'RAW' | 'code_page' ]] [[ , ] DATAFILETYPE [ = { 'char' | 'native'| 'widechar' | 'widenative' }]] [[ , ] FIELDTERMINATOR [ = 'field_terminator ' ]] [[ , ] FIRSTROW [ =first_row]] [[ , ] FIRETRIGGERS [ =fire_triggers]] [[ , ] FORMATFILE = 'format_file_path' ] [[ , ] KEEPIDENTITY ] [[ , ] KEEPNULLS ] [[ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]] [[ , ] LASTROW [ = last_row ] ] [[ , ] MAXERRORS [ =max_errors]] [[ , ] ORDER({column[ ASC | DESC ]}[,...n])] [[ , ] ROWS_PER_BATCH [ =rows_per_batch]] [[ , ] ROWTERMINATOR [ = 'row_terminator' ]] [[ , ] TABLOCK ] )]

必要參數 必要参数

資料檔案的位置可用data_file參數指定，但所指定的路徑必須是有效路徑。 资料档案的位置可用data_file参数指定，但所指定的路径必须是有效路径。

資料插入的資料庫位置由資料表定義或檢視表來決定。 资料插入的资料库位置由资料表定义或检视表来决定。 如前所述，您可以指定資料表或檢視表所在的資料庫名稱，或指定資料表或檢視表的擁有者。 如前所述，您可以指定资料表或检视表所在的资料库名称，或指定资料表或检视表的拥有者。 如果您試圖用BULK INSERT 指令將資料插入檢視表，只能影響檢視表中FROM 子句所參照底層資料表的其中一個。 如果您试图用BULK INSERT指令将资料插入检视表，只能影响检视表中FROM子句所参照底层资料表的其中一个。

選擇性使用的參數 选择性使用的参数

您可以選擇性的使用列在表24-3 的參數和關鍵字來修改BULK INSERT 的執行，這些選項和BCP 的選項很相似。 您可以选择性的使用列在表24-3的参数和关键字来修改BULK INSERT的执行，这些选项和BCP的选项很相似。

表24-3　BULK INSERT 選擇性使用的參數 表24-3BULK INSERT选择性使用的参数

可選擇性使用的參數 可选择性使用的参数	描述 描述
BATCHSIZE =size BATCHSIZE =size	指定每一筆批次操作中的列數，每筆批次操作皆視為一次交易。 指定每一笔批次操作中的列数，每笔批次操作皆视为一次交易。
CHECK_CONSTRAINTS	指定檢查條件約束限制，根據預設值，會忽略這些限制。 指定检查条件约束限制，根据预设值，会忽略这些限制。
CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]	指定資料檔中的字碼頁，只有在資料含有char、varchar或text資料型別時，CODEPAGE 才是有用意的。 指定资料档中的字码页，只有在资料含有char、varchar或text资料型别时，CODEPAGE才是有用意的。
DATAFILETYPE [ = 'char' | 'native' | 'widechar' | 'widenative' ] [ = 'char' | 'native' | 'widechar' | 'widenative' ]	指定資料檔中的資料型別，預設值的型別為char，其他的選項還包括native（原生資料型別）、widenative（Unicode 字元）和widenative（和native 原生資料型別相同，差別在char、varchar和text是以Unicode 儲存）。 指定资料档中的资料型别，预设值的型别为char，其他的选项还包括native（原生资料型别）、widenative（Unicode字元）和widenative（和native原生资料型别相同，差别在char、varchar和text是以Unicode储存）。
FIELDTERMINATOR [ =field_term] FIELDTERMINATOR [ =field_term]	指定要用於char與widechar資料型別檔中的欄位終止符號端子，預設值是/t（Tab 字元）。 指定要用于char与widechar资料型别档中的栏位终止符号端子，预设值是/t（Tab字元）。
FIRSTROW [ =first_row] FIRSTROW [ =first_row]	複製的第一個資料列編號，預設值為1，表示指定資料檔案中的第一個資料列。 复制的第一个资料列编号，预设值为1，表示指定资料档案中的第一个资料列。 如果要跳過在資料檔案中的標題資訊，就可使用此參數。 如果要跳过在资料档案中的标题资讯，就可使用此参数。
FORMATFILE [ =format_file] FORMATFILE [ =format_file]	指定格式檔的路徑。 指定格式档的路径。
KEEPIDENTITY	指定保留在匯入的資料檔案中呈現Identity 識別資料行的值。 指定保留在汇入的资料档案中呈现Identity识别资料行的值。
KEEPNULLS	指定空的資料行應保留Null 值。 指定空的资料行应保留Null值。
KILOBYTES_PER_BATCH [ =number] KILOBYTES_PER_BATCH [ =number]	指定在大量複製時，每一筆批次操作的千位組（KB）近似資料數。 指定在大量复制时，每一笔批次操作的千位组（KB）近似资料数。
LASTROW [ =last_row] LASTROW [ =last_row]	指定大量複製時，插入的最後一個資料列編號。 指定大量复制时，插入的最后一个资料列编号。 預設值為0，表示指定資料檔案的最後一個資料列。 预设值为0，表示指定资料档案的最后一个资料列。 如果只想複製特定的資料列，可以使用這個選項。 如果只想复制特定的资料列，可以使用这个选项。
MAXERRORS [ =max_errors] MAXERRORS [ =max_errors]	指定在大量複製插入結束之前，允許發生錯誤的最多次數，預設值為10。 指定在大量复制插入结束之前，允许发生错误的最多次数，预设值为10。
ORDER (column[ASC | DESC] ) ORDER (column[ASC | DESC] )	指定資料在資料檔中的排序方式。 指定资料在资料档中的排序方式。
ROWS_PER_BATCH [ =rows_per_batch]] ROWS_PER_BATCH [ =rows_per_batch]]	指定每一筆批次操作的資料列數目，每一筆批次會被視為單一交易複製。 指定每一笔批次操作的资料列数目，每一笔批次会被视为单一交易复制。 根據預值，所有在資料檔案夾的資料列，會被視為單一批次操作插入，執行一次認可。 根据预值，所有在资料档案夹的资料列，会被视为单一批次操作插入，执行一次认可。 當您執行大量插入時，可考慮指定這個選項，以在執行批次操作時，能解除資料表鎖定，允許執行其他處理程序步驟。 当您执行大量插入时，可考虑指定这个选项，以在执行批次操作时，能解除资料表锁定，允许执行其他处理程序步骤。
ROWTERMINATOR [ =row_term] ROWTERMINATOR [ =row_term]	要用於char與widechar資料型別檔的資料列終止符號端子，預設值是新增行字元（/n）。 要用于char与widechar资料型别档的资料列终止符号端子，预设值是新增行字元（/n）。

使用BULK INSERT 使用BULK INSERT

現在來看看使用BULK INSERT 陳述式的兩個範例，在這兩個範例中，我們將會從data.file載入資料至Northwind 資料庫的Customers資料表。 现在来看看使用BULK INSERT陈述式的两个范例，在这两个范例中，我们将会从data.file载入资料至Northwind资料库的Customers资料表。

---

說明 说明

BULK INSERT 陳述式只可用於將資料載入資料庫，不可用於擷取資料。 BULK INSERT陈述式只可用于将资料载入资料库，不可用于撷取资料。 由於BULK INSERT 的操作模式不如BCP 來的多，所以這裡的範例會比較少。 由于BULK INSERT的操作模式不如BCP来的多，所以这里的范例会比较少。

---

使用下列的T-SQL 陳述式，將資料載入資料庫： 使用下列的T-SQL陈述式，将资料载入资料库：

BULK INSERT Northwind..Customers FROM 'C:/data.file'  WITH       (       DATAFILETYPE = 'char'       ) 

GO

您可依需要加入更多的選項，以下的範例使用更多可選擇的參數： 您可依需要加入更多的选项，以下的范例使用更多可选择的参数：

BULK INSERT Northwind..Customers FROM 'C:/data.file'  WITH       (       BATCHSIZE = 5,       CHECK_CONSTRAINTS,       DATAFILETYPE = 'char',       FIELDTERMINATOR = '/t',       FIRSTROW = 5,       LASTROW = 20,       TABLOCK       ) 

這個陳述式只會從資料檔載入第5 到第20 個資料列。 这个陈述式只会从资料档载入第5到第20个资料列。 不管是不是預設值，欄位終止符號應指定為Tab 字元。 不管是不是预设值，栏位终止符号应指定为Tab字元。 該範例也檢測大量插入過程中限制條件，並在載入過程使用資料表鎖定，執行載入的交易將每5 個資料列視為一個批次單位。 该范例也检测大量插入过程中限制条件，并在载入过程使用资料表锁定，执行载入的交易将每5个资料列视为一个批次单位。