Share via


使用格式檔案大量匯入資料 (SQL Server)

此主題說明格式檔案在大量匯入作業中的用法。 格式檔案會將資料檔案的欄位對應到資料表的資料行。 您可以使用非 XML 或 XML 格式檔案,在使用 bcp 命令或 BULK INSERT 或 INSERT ... 時大量匯入資料SELECT * FROM OPENROWSET (BULK...) Transact-SQL 命令。

重要

如果格式檔案要與 Unicode 字元資料檔案搭配使用,則所有的輸入欄位都必須是 Unicode 文字字串 (也就是固定大小或以字元結束的 Unicode 字串)。

注意

如果您不熟悉格式檔案,請參閱非 XML 格式檔案 (SQL Server) XML 格式檔案 (SQL Server)

大量匯入命令的格式檔案選項

下表摘述每個大量匯入命令的格式檔案選項。

大量載入命令 使用格式檔案選項
BULK INSERT FORMATFILE = 'format_file_path'
INSERT ...SELECT * FROM OPENROWSET(BULK...) FORMATFILE = 'format_file_path'
bcp ... in -fformat_file

如需詳細資訊,請參閱 bcp 公用程式BULK INSERT (Transact-SQL) OPENROWSET (Transact-SQL)

注意

若要大量匯出或匯入 SQLXML 資料,請在格式檔案中使用下列其中一種資料類型:SQLCHAR 或 SQLVARYCHAR (資料會以用戶端字碼頁或定序所隱含的字碼頁傳送)、SQLNCHAR 或 SQLNVARCHAR (資料會以 Unicode 傳送),或是 SQLBINARY 或 SQLVARYBIN (資料不經轉換即傳送)。

範例

本節中的範例說明如何使用 bcp 命令和 BULK INSERT 和 INSERT ... 來使用格式檔案大量匯入資料SELECT * FROM OPENROWSET (BULK...) 語句。 執行其中一個大量匯入範例之前,必須先建立範例資料表、資料檔案與格式檔案。

範例資料表

這些範例需要在dbo架構下的 AdventureWorks2012 範例資料庫中建立名為myTestFormatFiles資料表的資料表。 若要建立此資料表,請在 SQL Server Management Studio 查詢編輯器 中執行:

USE AdventureWorks2012;  
GO  
CREATE TABLE myTestFormatFiles (  
   Col1 smallint,  
   Col2 nvarchar(50),  
   Col3 nvarchar(50),  
   Col4 nvarchar(50)  
   );  
GO  

範例資料檔

此範例使用範例資料檔案 myTestFormatFiles-c.Dat,包含下列記錄。 若要建立資料檔案,請在 Microsoft Windows 命令提示字元中輸入:

10,Field2,Field3,Field4  
15,Field2,Field3,Field4  
46,Field2,Field3,Field4  
58,Field2,Field3,Field4  

範例格式檔案

本節中有部分範例會使用 XML 格式檔案 myTestFormatFiles-f-x-c.Xml,而其他範例會使用非 XML 格式檔案。 兩種格式檔案都使用字元資料格式和非預設欄位結束字元 (,)。

非 XML 格式檔案範例

下列範例會使用 bcp,從 myTestFormatFiles 資料表產生 XML 格式檔案。 myTestFormatFiles.Fmt 檔案包含下列資訊:

9.0  
4  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     ","      2     Col2         SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100     ","      3     Col3         SQL_Latin1_General_CP1_CI_AS  
4       SQLCHAR       0       100     "\r\n"   4     Col4         SQL_Latin1_General_CP1_CI_AS  

若要使用 bcp 搭配 format 選項以建立此格式檔案,請在 Windows 命令提示字元中輸入:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T  
  

如需建立格式檔案的詳細資訊,請參閱建立格式檔案 (SQL Server)

XML 格式檔案範例

下列範例使用 bcp,從 myTestFormatFiles 資料表建立以產生 XML 格式檔案。 myTestFormatFiles.Xml 檔案包含下列資訊:

<?xml version="1.0"?>  
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
 <RECORD>  
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

若要使用 bcp 搭配 format 選項以建立此格式檔案,請在 Windows 命令提示字元中輸入:

bcp AdventureWorks2012..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T  

使用 bcp

下列範例會使用 bcp 將資料從 myTestFormatFiles-c.Dat 資料檔案 HumanResources.myTestFormatFiles 大量匯入範例資料庫中的資料表。 這個範例使用 XML 格式檔案 MyTestFormatFiles.Xml。 這個範例會在匯入資料檔之前,刪除任何現有的資料表資料列。

請在 Windows 命令提示字元之下,輸入:

bcp AdventureWorks2012..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T  

注意

如需此命令的詳細資訊,請參閱 bcp 公用程式

使用 BULK INSERT

下列範例使用 BULK INSERT 將資料從 myTestFormatFiles-c.Dat 資料檔案 HumanResources.myTestFormatFiles 大量匯入 AdventureWorks2012 範例資料庫中的資料表。 這個範例使用非 XML 格式檔案 MyTestFormatFiles.Fmt。 這個範例會在匯入資料檔之前,刪除任何現有的資料表資料列。

在 SQL Server Management Studio 查詢編輯器 中,執行:

USE AdventureWorks2012;  
GO  
DELETE myTestFormatFiles;  
GO  
BULK INSERT myTestFormatFiles   
   FROM 'C:\myTestFormatFiles-c.Dat'   
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');  
GO  
SELECT * FROM myTestFormatFiles;  
GO  

注意

如需此語句的詳細資訊,請參閱 BULK INSERT (Transact-SQL)

使用 OPENROWSET Bulk 資料列集提供者

下列範例使用 INSERT ... SELECT * FROM OPENROWSET(BULK...),將 myTestFormatFiles-c.Dat 資料檔中的資料大量匯入到 HumanResources.myTestFormatFiles 範例資料庫中的 AdventureWorks 資料表。 這個範例使用 XML 格式檔案 MyTestFormatFiles.Xml。 這個範例會在匯入資料檔之前,刪除任何現有的資料表資料列。

在 SQL Server Management Studio 查詢編輯器 中,執行:

USE AdventureWorks2012;  
DELETE myTestFormatFiles;  
GO  
INSERT INTO myTestFormatFiles  
    SELECT *  
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',  
      FORMATFILE='C:\myTestFormatFiles.Xml'       
      ) as t1 ;  
GO  
SELECT * FROM myTestFormatFiles;  
GO  

結束使用範例資料表時,可使用以下陳述式卸除該資料表:

DROP TABLE myTestFormatFiles  

注意

如需有關 OPENROWSET BULK 子句的詳細資訊,請參閱 OPENROWSET (Transact-SQL)

其他範例

建立格式檔案 (SQL Server)

使用格式檔案以略過資料表資料行 (SQL Server)

使用格式檔案跳過資料欄位 (SQL Server)

使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)

另請參閱

bcp 公用程式
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
非 XML 格式檔案 (SQL Server)
XML 格式檔案 (SQL Server)