Return to Revisions

The OPENROWSET documentation describes the SINGLE_CLOB option as:

returns the contents as a single-row, single-column rowset of type varchar(max)

So you will either need to split the string into multiple columns using the STRING_SPLIT function (or a similar method), use a format file to specify a row terminator for OPENROWSET, or use BULK INSERT, which allows you to specify a row terminator without the need to create a format file.

For example, you could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input file:

CREATE TABLE #bulkInsert (col1 NVARCHAR(MAX) NULL)

BULK INSERT #bulkInsert
FROM 'C:\Temp\myfile.csv'
WITH (FIELDTERMINATOR = N'|'
    , ROWTERMINATOR = N'0x0a' /* See https://stackoverflow.com/a/26758288/1582862 */
    , BATCHSIZE = 50000
    , TABLOCK
);