You can also tackle this problem using OPENROWSET BULK.

The data is first referenced using OPENROWSET BULK via the CTE cteFileData. This uses the unstructured format file DataSampleBulk.fmt which has a single field called BulkColumn defined as SQLCHAR 8000 and terminated with #

The second CTE cteData then splits the file vertically using CROSS APPLY/Tally split method and in the same query reassembles the data into the 8 column components using PIVOT. The final SELECT on cteData resolves each column into the correct data type

;

WITH cteFileData AS

(

SELECT BulkColumn FROM OPENROWSET (BULK 'C:\DataSample.txt', FORMATFILE = 'C:\DataSampleBulk.fmt') AS X

)

,

cteData AS

(

SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM cteFileData

CROSS APPLY

(

SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,

SUBSTRING(BulkColumn + ',', N, CHARINDEX(',', BulkColumn + ',', N) - N) AS Value

FROM master.dbo.Tally

WHERE N < LEN(BulkColumn) + 2 AND SUBSTRING(',' + BulkColumn + ',', N, 1) = ','

) AS Z

PIVOT

(

MAX(Value) FOR ROW IN

(

[1],[2],[3],[4],[5],[6],[7],[8]

)

)

AS pvt

)

AS Y

)

SELECT

CONVERT(VARCHAR(20), [1]) AS COL1,

CONVERT(DATETIME, [2]) AS COL2,

CONVERT(DECIMAL(9,2), [3]) AS COL3,

CONVERT(DECIMAL(9,2), [4]) AS COL4,

CONVERT(DECIMAL(9,2), [5]) AS COL5,

CONVERT(DECIMAL(9,2), [6]) AS COL6,

CONVERT(BIGINT, [7]) AS COL7,

CONVERT(DECIMAL(9,2), [8]) AS COL8

FROM cteData