使用格式化文件大容量导入数据 (SQL Server)Use a Format File to Bulk Import Data (SQL Server)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

本主题说明如何在大容量导入操作中使用格式化文件。This topic illustrates the use of a format file in bulk-import operations. 格式化文件可将数据文件的各字段映射到表的各列。A format file maps the fields of the data file to the columns of the table. 有关其他信息,请查看 创建格式化文件 (SQL Server)Please review Create a Format File (SQL Server) for additional information.

轮廓Outline
开始之前Before You Begin
示例测试条件Example Test Conditions
 ● 示例表 ● Sample Table
 ● 示例数据文件 ● Sample Data File
创建格式化文件Creating the Format Files
 ● 创建非 XML 格式化文件 ● Creating a Non-XML Format File
 ● 创建 XML 格式化文件 ● Creating an XML Format File
使用格式化文件批量导入数据Using a Format File to Bulk Import Data
 ● 使用 bcp 和非 XML 格式化文件 ● Using bcp and Non-XML Format File
 ● 使用 bcp 和 XML 格式化文件 ● Using bcp and XML Format File
 ● 使用 BULK INSERT 和非 XML 格式化文件 ● Using BULK INSERT and Non-XML Format File
 ● 使用 BULK INSERT 和 XML 格式化文件 ● Using BULK INSERT and XML Format File
 ● 使用 OPENROWSET(BULK...) 和非 XML 格式化文件 ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● 使用 OPENROWSET(BULK...) 和 XML 格式化文件 ● Using OPENROWSET(BULK...) and XML Format File

开始之前Before You Begin

  • 对于用于 Unicode 字符数据文件的格式化文件,所有输入字段必须为 Unicode 文本字符串(即固定大小 Unicode 字符串或字符终止 Unicode 字符串)。For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
  • 若要批量导出或导入 SQLXML 数据,请在格式化文件中使用下列数据类型之一:To bulk export or import SQLXML data, use one of the following data types in your format file:
    • SQLCHAR 或 SQLVARYCHAR(在客户端代码页或排序规则隐含的代码页中发送数据)SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation)
    • SQLNCHAR 或 SQLNVARCHAR(以 Unicode 格式发送数据)SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
    • SQLBINARY 或 SQLVARYBIN(不经任何转换即发送数据)。SQLBINARY or SQLVARYBIN (the data is sent without any conversion).
  • Azure SQL 数据库和 Azure SQL 数据仓库仅支持 bcpAzure SQL Database and Azure SQL Data Warehouse only support bcp. 有关其他信息,请参阅:For additional information, see:

示例测试条件Example Test Conditions

本主题中格式化文件示例基于下面定义的表和数据文件。The examples of format files in this topic are based on the table and data file defined below.

示例表Sample Table

下面的脚本创建一个测试数据库和一个名为 myFirstImport的表。The script below creates a test database and a table named myFirstImport. 在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

示例数据文件Sample Data File

使用记事本创建一个空文件 D:\BCP\myFirstImport.bcp 并插入以下数据:Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

还可通过执行以下 PowerShell 脚本创建和填充数据文件:Alternatively, you can execute the following PowerShell script to create and populate the data file:

Clear-Host
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

创建格式化文件Creating the Format Files

SQL Server 支持两种类型的格式化文件:非 XML 格式和 XML 格式。SQL Server support two types of format file: non-XML format and XML format. 非 XML 格式是 SQL Server 早期版本支持的原始格式。The non-XML format is the original format that is supported by earlier versions of SQL Server.

创建非 XML 格式化文件Creating a Non-XML Format File

有关详细信息,请查看 非 XML 格式化文件 (SQL Server)Please review Non-XML Format Files (SQL Server) for detailed information. 下面的命令基于 的架构使用 bcp 实用工具 myFirstImport.fmt生成非 XML 格式化文件 myFirstImportThe following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. 若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. 格式化选项还需要 -f 选项。The format option also requires the -f option. 此外,对于本示例,限定符 c 用于指定字符数据, t, 用于将逗号指定为 字段终止符,而 T 用于指定使用集成安全性的受信任连接。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

非 XML 格式化文件 D:\BCP\myFirstImport.fmt 应如下所示:Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

重要

确保非 XML 格式化文件以回车符/换行符结尾。Ensure your non-XML format file ends with a carriage return\line feed. 否则可能会收到以下错误消息:Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

创建 XML 格式化文件Creating an XML Format File

有关详细信息,请查看 XML 格式化文件 (SQL Server)Please review XML Format Files (SQL Server) for detailed information. 下面的命令使用 bcp 实用工具 基于 myFirstImport.xml的架构创建 xml 格式化文件 myFirstImportThe following command will use the bcp utility to create an xml format file, myFirstImport.xml, based on the schema of myFirstImport. 若要使用 bcp 命令创建格式化文件,请指定 format 参数,并使用 nul 而不是数据文件路径。To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. format 选项始终要求 -f 选项,若要创建 XML 格式化文件,还必须指定 -x 选项。The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. 此外,对于本示例,限定符 c 用于指定字符数据, t, 用于将逗号指定为 字段终止符,而 T 用于指定使用集成安全性的受信任连接。In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. 在命令提示符处输入以下命令:At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

XML 格式化文件 D:\BCP\myFirstImport.xml 应如下所示:Your XML format file, D:\BCP\myFirstImport.xml should look as follows:

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

使用格式化文件批量导入数据Using a Format File to Bulk Import Data

下面的示例使用上面创建的数据库、数据文件和格式化文件。The examples below use the database, datafile, and format files created above.

使用 bcp非 XML 格式化文件 Using bcp and Non-XML Format File

在命令提示符处输入以下命令:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

使用 bcpXML 格式化文件 Using bcp and XML Format File

在命令提示符处输入以下命令:At a command prompt, enter the following command:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

使用 BULK INSERT非 XML 格式化文件 Using BULK INSERT and Non-XML Format File

在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

使用 BULK INSERTXML 格式化文件 Using BULK INSERT and XML Format File

在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport   
   FROM 'D:\BCP\myFirstImport.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO  

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

使用 OPENROWSET(BULK...)非 XML 格式化文件 Using OPENROWSET(BULK...) and Non-XML Format File

在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

使用 OPENROWSET(BULK...)XML 格式化文件 Using OPENROWSET(BULK...) and XML Format File

在 Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) 中执行以下 Transact-SQL:Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

更多示例!More examples!

创建格式化文件 (SQL Server)Create a Format File (SQL Server)
使用格式化文件跳过表列 (SQL Server)Use a Format File to Skip a Table Column (SQL Server)
使用格式化文件跳过数据字段 (SQL Server)Use a Format File to Skip a Data Field (SQL Server)
使用格式化文件将表列映射到数据文件字段 (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

另请参阅See also

bcp 实用工具 bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
非 XML 格式化文件 (SQL Server) Non-XML Format Files (SQL Server)
XML 格式化文件 (SQL Server)XML Format Files (SQL Server)
用来导入或导出数据的格式化文件 (SQL Server)Format Files for Importing or Exporting Data (SQL Server)