批量插入（Bulk Insert）中的行分隔符问题

嘿，我来帮你解决这个批量插入的行分隔符难题！先理清楚你的场景：因为字段里包含逗号，怕用CSV格式出问题，所以选了|当字段分隔符，但执行Bulk Insert时，SQL Server没法正确识别每行数据的边界，导致插入出错，对吧？下面给你一步步的解决方案：

第一步：先确保数据有清晰的行分隔符 #

从你给的示例数据来看，两行记录是连在一起的：

12007|0|10|70|0|2017|1|1|Some County, Nevada|Total Covered|10 Total,all industries12007|0|10|70|0|2017|2|1|Some County, Nevada|Total Covered|10 Total,all industries

这明显是没有行结束标记，SQL Server根本不知道哪里是一行的结尾。所以首先要让每条记录单独占一行，用换行符（\n）或者回车换行（\r\n）分隔。

你可以用文本编辑器（比如Notepad++）打开数据文件，开启“显示所有字符”功能（视图→显示符号→显示所有字符），检查是否有\r或\n的标记。如果没有，就得先预处理数据：

• 要是数据量小，手动拆分每行；
• 数据量大的话，用脚本批量处理，比如Python脚本：

import re

# 读取源数据
with open("source_data.txt", "r", encoding="utf-8") as f:
    raw_content = f.read()

# 假设每条记录以5位数字+|开头，用正则拆分
records = re.split(r"(?=\d{5}\|)", raw_content)[1:]

# 写入带换行的新文件
with open("formatted_data.txt", "w", encoding="utf-8") as f:
    f.write("\n".join(records))

第二步：正确配置Bulk Insert语句 #

当数据文件有了统一的行分隔符后，在Bulk Insert里明确指定字段和行的分隔符就行。比如你的数据用\r\n当行分隔符，语句如下：

BULK INSERT test
FROM 'C:\path\to\formatted_data.txt'
WITH (
    FIELDTERMINATOR = '|',  -- 指定字段分隔符为|
    ROWTERMINATOR = '\r\n', -- 匹配你的数据行结束符
    FIRSTROW = 1,           -- 要是没有表头就设为1，有表头就设为2
    CODEPAGE = 'RAW'        -- 保持字符编码一致，避免乱码
);

如果你的行分隔符是单个\n，就把ROWTERMINATOR改成'\n'。

第三步：处理特殊情况——字段里包含换行符 #

要是你的字段内容里意外带了换行符（比如某个地址字段里有换行），那即使加了行分隔符，SQL Server还是会把字段里的换行当成行结束，导致解析错误。这种情况有两种解决办法：

1. 清洗数据：把字段里的换行符替换成空格或者其他不冲突的字符，比如用Python脚本批量替换；
2. 用格式文件精确解析：写一个XML格式的格式文件，告诉SQL Server每个字段的分隔规则，不受换行干扰。

举个简单的格式文件示例（对应你的11个字段）：

<?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="5"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="1"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="2"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="2"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="1"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="1"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="1"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="area_fips" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="2" NAME="own_code" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="industry_code" xsi:type="SQLVARCHAR"/>
  <!-- 剩下的字段依次对应表中的列名 -->
  <COLUMN SOURCE="11" NAME="your_last_column_name" xsi:type="SQLVARCHAR"/>
 </ROW>
</BCPFORMAT>

然后在Bulk Insert里引用这个格式文件：

BULK INSERT test
FROM 'C:\path\to\formatted_data.txt'
WITH (
    FORMATFILE = 'C:\path\to\your_format_file.xml'
);

最后：验证数据格式 #

每次调整后，先拿一小部分数据测试Bulk Insert，确认没有错误再批量插入。如果还是有问题，就检查数据文件里是否有特殊字符（比如不可见的控制字符），可以用文本编辑器的“显示所有字符”功能排查。

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