如何用BULK INSERT从制表符分隔文件加载指定列至窄表

这个场景完全可行，通过自定义格式文件配合BULK INSERT，可以精准提取宽文件中的指定列，甚至将同一文件列映射到表的多个列。以下是针对你的需求的两种实现方案：

一、XML格式文件（推荐，可读性更强） #

创建名为LoadNarrowTable.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>
  <!-- 完整定义文件的50列，仅标记需要加载的列的具体属性 -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/> <!-- 文件Column1 -->
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/> <!-- 文件Column2 - 忽略 -->
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/> <!-- 文件Column3 - 忽略 -->
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/> <!-- 文件Column4 - 忽略 -->
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255"/> <!-- 文件Column5 -->
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/> <!-- 文件Column6 - 忽略 -->
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255"/> <!-- 文件Column7 -->
  <!-- 补充定义文件Column8到Column49，全部设置为忽略 -->
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="0"/>
  <!-- ... 此处省略ID10至ID49的重复定义 -->
  <FIELD ID="50" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="0"/> <!-- 文件最后一列，终止符为换行 -->
 </RECORD>
 <ROW>
  <!-- 映射表列到指定的文件列 -->
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/> <!-- 表ID → 文件Column1 -->
  <COLUMN SOURCE="5" NAME="var1" xsi:type="SQLVARCHAR"/> <!-- 表var1 → 文件Column5 -->
  <COLUMN SOURCE="7" NAME="var2" xsi:type="SQLVARCHAR"/> <!-- 表var2 → 文件Column7 -->
  <COLUMN SOURCE="7" NAME="var3" xsi:type="SQLVARCHAR"/> <!-- 表var3 → 文件Column7（同一文件列映射到多列） -->
 </ROW>
</BCPFORMAT>

关键点说明： #

• <RECORD>块必须完整定义文件的所有50列，忽略的列设置MAX_LENGTH="0"即可
• <ROW>块仅定义需要加载到表中的列，通过SOURCE属性关联对应的文件列ID
• 同一文件列映射到多个表列，只需在<ROW>中添加多个<COLUMN>节点并指向相同的SOURCEID

二、非XML格式文件（旧版兼容） #

创建名为LoadNarrowTable.fmt的格式文件，内容如下：

10.0
50
1       SQLCHAR             0       12      "\t"     1     ID                  ""
2       SQLCHAR             0       0       "\t"     0     Column2             ""
3       SQLCHAR             0       0       "\t"     0     Column3             ""
4       SQLCHAR             0       0       "\t"     0     Column4             ""
5       SQLCHAR             0       255     "\t"     2     var1                SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       0       "\t"     0     Column6             ""
7       SQLCHAR             0       255     "\t"     3     var2                SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       255     "\t"     4     var3                SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             0       0       "\t"     0     Column8             ""
9       SQLCHAR             0       0       "\t"     0     Column9             ""
...
50      SQLCHAR             0       0       "\r\n"   0     Column50            ""

关键点说明： #

• 第一行是SQL Server版本号（如10.0对应2008版，可根据你的版本调整）
• 第二行是文件总列数（必须为50）
• 每行字段含义：文件列序号 数据类型 前缀长度 最大长度 终止符 表列序号 表列名 排序规则
• 忽略的列将表列序号设为0，表列名可随意填写
• 同一文件列映射多表列，需重复行并设置不同的表列序号（对应表中列的顺序：ID=1，var1=2，var2=3，var3=4）

三、执行BULK INSERT命令 #

使用以下SQL语句执行数据加载（根据格式文件类型调整路径）：

BULK INSERT T_Narrow
FROM 'D:\Data\WideTabSeparatedFile.tsv'
WITH (
    FORMATFILE = 'D:\FormatFiles\LoadNarrowTable.xml', -- 若用非XML格式则替换为'LoadNarrowTable.fmt'
    FIRSTROW = 2, -- 若文件包含表头则设为2，无表头设为1
    CODEPAGE = '65001' -- 若文件是UTF-8编码需添加此参数，否则可省略
);

注意事项： #

• 确保格式文件中的终止符与实际文件一致（如Unix换行用'\n'，Windows用'\r\n'）
• 即使文件列数超过1024，只要格式文件完整定义所有列，BULK INSERT只会加载你指定的列，不会触发列数限制问题
• 可先用bcp命令测试格式文件有效性：bcp T_Narrow in D:\Data\WideTabSeparatedFile.tsv -f D:\FormatFiles\LoadNarrowTable.xml -S YourServerName -d YourDatabaseName -T

内容的提问来源于stack exchange，提问作者Gabriela Nanau