使用BULK INSERT导入数据时的日期与文本类型异常问题

背景与操作步骤 #

我有超过1000条数据集需要导入SSMS，最初使用以下T-SQL语句创建表并导入数据：

CREATE TABLE sales(
  SPID text,
  GeoID text,
  PID text,
  SaleDate smalldatetime,
  Amount int,
  Customers int,
  Boxes int);

BULK INSERT sales
FROM 'C:\Users\alvin_zoj6s4b\Downloads\sales.txt'
WITH (FIELDQUOTE = '\' 
      , FIELDTERMINATOR = ','
      , ROWTERMINATOR = '\n');

执行后出现错误（错误提示：转换日期和/或时间从字符串时失败）。

发现问题出在SaleDate列后，我将该列数据类型改为TEXT，修改后的建表语句如下：

CREATE TABLE sales(
  SPID text,
  GeoID text,
  PID text,
  SaleDate TEXT,
  Amount int,
  Customers int,
  Boxes int);

此时表中所有文本类型列（SPID、GeoID、PID、SaleDate）均显示为空值，数值类型列数据正常。

本地文本文件结构：每行数据以逗号分隔，文本类型字段用单引号包裹，日期格式为'MM/DD/YYYY'（例如'01/05/2023'）。

疑问 #

1. 导入SSMS时，文本类型数据应保留原始单引号，但为何输出显示为空值？
2. SaleDate列仅改为TEXT类型才能导入，我哪里操作有误？

解答 #

针对疑问1：文本列空值问题 #

你的BULK INSERT语句中FIELDQUOTE = '\'配置错误——文本文件里用的是单引号作为字段引号，而你设置的是反斜杠。BULK INSERT会把反斜杠识别为字段的包裹符号，但你的数据里没有反斜杠，导致它无法正确解析文本字段，最终把这些列识别为空。

正确配置应为FIELDQUOTE = ''''（T-SQL中单引号需用两个单引号转义），这样就能正确识别数据里的单引号包裹内容，导入后文本列会自动去掉外层单引号（若需保留单引号，可后续用字符串拼接处理）。

针对疑问2：日期列导入失败问题 #

最初用smalldatetime类型失败，是因为日期格式不匹配：

• smalldatetime默认支持YYYY-MM-DD或符合SQL Server默认日期格式的字符串，而你的数据是MM/DD/YYYY格式且被单引号包裹。

解决方法有两种：

1. 修改BULK INSERT日期格式配置：在WITH子句中添加DATEFORMAT = 'mdy'，让SQL Server识别MM/DD/YYYY格式，配合正确的FIELDQUOTE配置，即可直接用smalldatetime类型导入：

BULK INSERT sales
FROM 'C:\Users\alvin_zoj6s4b\Downloads\sales.txt'
WITH (FIELDQUOTE = '''' 
      , FIELDTERMINATOR = ','
      , ROWTERMINATOR = '\n'
      , DATEFORMAT = 'mdy');

2. 先导入为TEXT再转换：若暂时不想调整日期格式，先导入为TEXT类型，之后用CONVERT函数转换为smalldatetime：

UPDATE sales 
SET SaleDate = CONVERT(smalldatetime, SaleDate, 101); -- 101对应MM/DD/YYYY格式

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