How to direct the output of OPENROWSET() to an argument of an INSERT statement?

Question

Context

I have ~32.25 million, 2KB sensor binary data files (amounting to 32.25E6*2/(1024^3) = 61.5 GB) of data that I'd like to put into a Microsoft SQL database table (Microsoft SQL Server 2014). The table is defined using:

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

where Ensemble stores 2 KB of data. I'm writing a T-SQL query to test-insert a record into that table. Once successful, I will programmatically insert data into the table using Python and pyodbc.

Problem Statement

Per this advice, I tried to create a similar query and encountered the error:

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

while trying to execute:

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)
    )

I think it has to do with the syntax of the SELECT subquery that calls OPENROWSET(). The subquery itself executes OK on its own:

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

0x7F7F7F0500081...

Solution

PeterHe's suggestion resolved the error. I changed the database table declaration so that all nvarchar read 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)
)

The error seems to be telling you the problem here; you're going to truncate a value. Clearly your columns aren't find enough in the table `station_binaries`. — Thom A, Sep 19 '19 at 13:45

score 1 · Accepted Answer · answered Sep 19 '19 at 13:48

1

Ensemble varbinary should be Ensemble varbinary(max).

answered Sep 19 '19 at 13:48

PeterHe

2,766
1
8
7

I deleted and recreated the table with `Ensemble varbinary(MAX)` but get the same error. – Minh Tran Sep 19 '19 at 13:56
1

Also all the nvarchar columns, you need to give a length, e.g. Filename nvarchar(128) – PeterHe Sep 19 '19 at 13:59

How to direct the output of OPENROWSET() to an argument of an INSERT statement?

Context

Problem Statement

Solution

1 Answers1