SQL Server导入smallmoney类型数据时转换错误求助

这个问题我之前帮团队处理过好几次，核心原因很明确：SQL Server的smallmoney类型默认不识别带$前缀的数值，批量导入时会把$当成非法字符，直接抛出类型不匹配的错误（也就是你遇到的Msg 4864）。给你三个实用的解决思路，按需选择：

1. 预处理CSV，批量移除$符号 #

最简单直接的方式就是先清理CSV文件里的$符号。如果是手动处理，用Excel打开CSV，选中BillingRate列，用查找替换功能把$替换为空就行；如果是需要自动化处理，用PowerShell脚本几行就能搞定：

# 读取原CSV，移除所有$符号后保存为新文件
(Get-Content "D:\data\your_data.csv") -replace '\$', '' | Set-Content "D:\data\cleaned_data.csv"

处理后的CSV里BillingRate列就变成纯数字格式（比如203.75），再导入就不会触发类型错误了。

2. 通过临时表中转，导入后再转换数据 #

如果不方便修改原CSV文件，可以先把数据导入到一个临时表（把BillingRate设为varchar类型），然后再清理字符并转换为smallmoney插入正式表。这种方式灵活性很高，就算数据里还有其他格式问题（比如千分位逗号）也能一并处理：

-- 创建临时导入表，所有列用字符串类型兼容CSV格式
CREATE TABLE #TempBillingData (
    ID VARCHAR(50),
    EmployeeName VARCHAR(100),
    Department VARCHAR(50),
    BillingRate VARCHAR(20)
);

-- 批量导入CSV到临时表
BULK INSERT #TempBillingData
FROM 'D:\data\your_data.csv'
WITH (
    FIRSTROW = 2, -- 如果CSV第一行是表头就设为2，否则设为1
    FIELDTERMINATOR = ',', -- CSV的列分隔符，根据你的文件调整
    ROWTERMINATOR = '\r\n'
);

-- 清理数据并插入正式表
INSERT INTO Your正式表 (ID, EmployeeName, Department, BillingRate)
SELECT 
    ID,
    EmployeeName,
    Department,
    -- 先移除$和可能的千分位逗号，再转换为smallmoney
    CAST(REPLACE(REPLACE(BillingRate, '$', ''), ',', '') AS SMALLMONEY) AS BillingRate
FROM #TempBillingData;

-- 清理临时表
DROP TABLE #TempBillingData;

3. 使用格式文件（Format File）指定列处理规则 #

如果是频繁的批量导入场景，可以创建一个XML格式文件，告诉SQL Server如何解析带$的BillingRate列。举个适配你场景的格式文件示例（假设你的表有4列，第4列是BillingRate）：

<?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="50"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="EmployeeName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Department" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BillingRate" xsi:type="SQLSMALLMONEY"/>
 </ROW>
</BCPFORMAT>

然后在BULK INSERT时指定这个格式文件：

BULK INSERT Your正式表
FROM 'D:\data\your_data.csv'
WITH (
    FORMATFILE = 'D:\data\billing_format.xml',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n'
);

额外提醒 #

如果你的CSV里金额还带有千分位逗号（比如$1,203.75），记得在转换前先移除逗号，不然同样会触发转换错误，就像上面临时表方法里写的那样用两次REPLACE处理。

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