如何将OPENROWSET()输出作为INSERT语句参数插入SQL Server表

问题描述 #

我正在处理大规模传感器数据导入任务：需要将3225万个2KB的二进制文件（总计约61.5GB）导入SQL Server 2014的station_binaries表中。最初的表结构定义如下：

CREATE TABLE station_binaries (
 StationID int,
 StationName nvarchar,
 Timestamp datetime2(7),
 InstrumentNumber int,
 Folder nvarchar,
 Filename nvarchar,
 Ensemble varbinary(MAX)
)

其中Ensemble字段负责存储每个文件的2KB二进制内容。在测试单条插入时，遇到了截断错误：

Msg 8152, Level 16, State 4, Line 2 String or binary data would be truncated. The statement has been terminated.

执行的插入语句如下：

INSERT INTO GCOOS.dbo.station_binaries (StationID, StationName, [Timestamp], InstrumentNumber, Folder, [Filename], Ensemble)
VALUES (
 42861,
 'DeepwaterNautilus',
 DATETIME2FROMPARTS(2005,5,2,0,3,0,0,0),
 0,
 'C:\Users\mtran\Desktop\...\ndbc_data\_42861_Deepwater_Nautilus\2005\05\02\',
 '428610200505020003.bin',
 (SELECT * FROM OPENROWSET(BULK 'C:\Users\...\42861_Deepwater_Nautilus\2005\05\02\428610200505020003.bin', SINGLE_BLOB) result)
)

一开始我怀疑是OPENROWSET的子查询语法问题，但单独执行该子查询完全正常，能正确返回二进制数据：

SELECT * FROM OPENROWSET(BULK 'C:\Users\...\42861_Deepwater_Nautilus\2005\05\02\428610200505020003.bin', SINGLE_BLOB) result

解决方法 #

经过排查，问题根源在于未指定长度的nvarchar字段：在SQL Server中，定义nvarchar但不指定长度时，默认长度仅为1！这导致StationName、Folder、Filename这些字段根本无法容纳实际要插入的字符串内容，从而触发截断错误。

按照PeterHe的建议，我将所有nvarchar字段修改为nvarchar(MAX)，以支持任意长度的字符串存储。修改后的表定义如下：

CREATE TABLE station_binaries (
 StationID int,
 StationName nvarchar(MAX),
 Timestamp datetime2(7),
 InstrumentNumber int,
 Folder nvarchar(MAX),
 Filename nvarchar(MAX),
 Ensemble varbinary(MAX)
)

修改完成后，重新执行插入语句成功，后续计划的Python+pyodbc批量插入也能正常推进。

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