使用格式化文件批量导入数据 (SQL Server)

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

本文说明如何在批量导入操作中使用格式化文件。 格式化文件可将数据文件的各字段映射到表的各列。 有关其他信息，请查看 使用 bcp （SQL Server） 创建格式化文件。

先决条件

• 若要在 SQL Server 实例上运行 T-SQL 命令，请使用 SQL Server Management Studio （SSMS）、 Visual Studio Code 的 MSSQL 扩展、 sqlcmd 或你喜欢的 T-SQL 查询工具。

• 对于用于 Unicode 字符数据文件的格式化文件，所有输入字段必须为 Unicode 文本字符串（即固定大小 Unicode 字符串或字符终止 Unicode 字符串）。

• 若要批量导出或 导入 XML 文档（SQL Server）数据的批量导入和导出示例 ，请在格式化文件中使用以下数据类型之一：

  • SQLCHAR 或 SQLVARCHAR（在客户端代码页或排序规则隐含的代码页中发送数据）
  • SQLNCHAR 或 SQLNVARCHAR（以 Unicode 格式发送数据）
  • SQLBINARY 或 SQLVARBIN（不经任何转换即发送数据）。

• Azure SQL 数据库和 Azure Synapse Analytics 仅支持 bcp。 如需其他信息，请参阅：

  • 将数据加载到 Azure Synapse Analytics 中
  • 将数据从 SQL Server 载入 Azure Synapse Analytics（平面文件）
  • 迁移数据

• Azure Synapse Analytics不支持此语法（包括批量插入）。 在 Azure Synapse Analytics 和其他云数据库平台集成中，通过 Azure 数据工厂中的 COPY 语句或使用 T-SQL 语句（如 COPY INTO）和 PolyBase 完成数据移动。

示例测试条件

本主题中格式化文件示例基于下面定义的表和数据文件。

示例表

下面的脚本创建一个测试数据库和一个名为 myFirstImport的表。 在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

CREATE DATABASE TestDatabase;
GO

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

示例数据文件

使用记事本创建一个空文件 D:\BCP\myFirstImport.bcp 并插入以下数据：

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

还可通过执行以下 PowerShell 脚本创建和填充数据文件：

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;

创建格式化文件

SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。 非 XML 格式是 SQL Server 早期版本支持的原始格式。

将代码示例的本地文件位置更改为计算机上的文件位置。

创建非 XML 格式化文件

有关详细信息，请查看 “使用非 XML 格式化文件”（SQL Server ）。 下面的命令基于 的架构使用 bcp 实用工具 myFirstImport.fmt生成非 XML 格式化文件 myFirstImport。

• 若要使用 bcp 命令创建格式化文件，请指定 format 参数并使用 nul 而不是数据文件路径。
• 格式选项还需要使用 -f 选项。
• c 用于指定字符数据
• t, 用于将逗号指定为 字段终止符
• T 用于使用集成安全性指定受信任的连接。

在命令提示符处输入以下命令：

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 应如下所示：

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 格式化文件以回车符/换行符结尾。 否则可能会收到以下错误消息：

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

创建 XML 格式化文件

有关详细信息 ，请查看 XML 格式化文件（SQL Server ）。 下面的命令使用 bcp 实用工具 基于 myFirstImport.xml的架构创建 xml 格式化文件 myFirstImport。

• 若要使用 bcp 命令创建格式化文件，请指定 format 参数并使用 nul 而不是数据文件路径。
• 格式选项始终需要该 -f 选项。
• 若要创建 XML 格式化文件，还必须指定该 -x 选项。
• c 用于指定字符数据
• t, 用于将逗号指定为 字段终止符
• T 用于使用集成安全性指定受信任的连接。

在命令提示符处输入以下命令：

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 应如下所示：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://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="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

使用格式化文件批量导入数据

下面的示例使用上面创建的数据库、数据文件和格式化文件。

使用 bcp 并使用 非 XML 格式化文件 （SQL Server）

在命令提示符处输入以下命令：

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"

使用 bcp 和 XML 格式化文件 （SQL Server）

在命令提示符处输入以下命令：

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 （Transact-SQL） 和使用 非 XML 格式化文件 （SQL Server）

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

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 INSERT（Transact-SQL） 和 XML 格式化文件（SQL Server）

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

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 （Transact-SQL） 和使用 非 XML 格式化文件 （SQL Server）

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

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（Transact-SQL） 和 XML 格式化文件（SQL Server）

在 Microsoft SQL Server Management Studio (SSMS) 中执行以下 Transact-SQL：

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;

更多示例

• 使用 bcp 创建格式化文件（SQL Server）
• 使用格式化文件跳过表列 (SQL Server)
• 使用格式文件跳过数据字段 (SQL Server)
• 使用格式文件将表列映射到数据文件字段 (SQL Server)

相关内容

• bcp 实用工具
• BULK INSERT （Transact-SQL）
• OPENROWSET BULK （Transact-SQL）
• 使用非 XML 格式文件（SQL Server）
• XML 格式化文件 （SQL Server）
• 格式化文件以导入或导出数据（SQL Server）