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.
You could adapt the following code by customizing the
ROWTERMINATOR as necessary for your input file. For example, the following code was tested on your provided sample file:
CREATE TABLE #bulkInsert (col1 NVARCHAR(MAX) NULL)
BULK INSERT #bulkInsert
WITH (FIELDTERMINATOR = N','
, ROWTERMINATOR = N'0x0a' /* See https://stackoverflow.com/a/26758288/1582862 */
, BATCHSIZE = 50000
-- The sample rows were correctly bulk loaded, including the header row,
-- which you can remove from your file or filter out after bulk loading