CREATE TABLE [dbo].[DIM_TEST](
	[ID] [int] NULL,
	[Data] [varchar](max) NULL
) 

go

CREATE TABLE [dbo].[DIM_TEST_1](
	[ItemID] [int] NULL,
	[Item] [varchar](500) NULL
)

DELETE FROM dbo.DIM_TEST_1
go

DECLARE @Str AS VARCHAR(MAX)

SET @Str = (SELECT Data FROM dbo.DIM_TEST WHERE ID = 100)

INSERT INTO dbo.DIM_TEST_1
SELECT * FROM dbo.fnDelimitedSplit(@Str,'#')
go

SELECT * FROM dbo.DIM_TEST_1 ORDER BY ItemID DESC

 CREATE TABLE #MyHead
        (
        Col1 VARCHAR(10),
        Col2 DATETIME,
        Col3 DECIMAL(9,2),
        Col4 DECIMAL(9,2),
        Col5 DECIMAL(9,2),
        Col6 DECIMAL(9,2),
        Col7 BIGINT,
        Col8 DECIMAL(9,2)
)
;
   BULK INSERT #MyHead
   FROM 'C:\Temp\DataSample.txt' 
   WITH (
        CODEPAGE        = 'RAW',
        DATAFILETYPE    = 'CHAR',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR   = '#',
        TABLOCK
        )
;

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 475 ms.

(20485 row(s) affected)

--2D Split Combined with Refactor to Table
 
--===== Load the passed parameter
DECLARE @Parameter VARCHAR(MAX); 
    SET @Parameter = (SELECT Data FROM dbo.DIM_TEST);

--===== Suppress the auto-display of rowcounts to keep them from being
     -- mistaken as part of the result set.
    SET NOCOUNT ON;

--===== Add a start comma to the Parameter and change all "group"
     -- delimiters to a comma so we can handle all the elements the same way.
    SET @Parameter = ','+REPLACE(@Parameter,'#',',');

--===== Do the split with some row numbering
WITH -- This stuff makes an "inline" Tally "table"...
      E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000
      E8(N) AS (SELECT 1 FROM E4 a, E4 b),   --100,000,000
     E16(N) AS (SELECT 1 FROM E8 a, E8 b),   --10,000,000,000,000,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E16),  
cteSplit AS
( --=== This does the split and vertical stacking...
 SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
        SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS Element
   FROM cteTally
  WHERE N < LEN(@Parameter)
    AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
)
 --==== ... and this puts the data back together in a fixed table format
     -- using classic Cross-Tab code. It also converts columns that are
     -- supposed to be numeric (Jeff Moden)
 SELECT (RowNumber/8)+1 AS RowNumber,      
        CAST(MAX(CASE WHEN RowNumber%8 = 0 THEN Element END) AS VARCHAR(10))  AS [Symbol],
        CAST(MAX(CASE WHEN RowNumber%8 = 1 THEN Element END) AS DATETIME)     AS [Date],
        CAST(MAX(CASE WHEN RowNumber%8 = 2 THEN Element END) AS DECIMAL(9,2)) AS [Open],
        CAST(MAX(CASE WHEN RowNumber%8 = 3 THEN Element END) AS DECIMAL(9,2)) AS [High],
        CAST(MAX(CASE WHEN RowNumber%8 = 4 THEN Element END) AS DECIMAL(9,2)) AS [Low],
        CAST(MAX(CASE WHEN RowNumber%8 = 5 THEN Element END) AS DECIMAL(9,2)) AS [Close],
        CAST(MAX(CASE WHEN RowNumber%8 = 6 THEN Element END) AS BIGINT)       AS [Volume],
        CAST(MAX(CASE WHEN RowNumber%8 = 7 THEN Element END) AS DECIMAL(9,2)) AS [AdjClose]
   INTO dbo.JBMTest
   FROM cteSplit
  GROUP BY RowNumber/8;

 SELECT TOP 10 * FROM dbo.JBMTest;
 SELECT COUNT (*) FROM dbo.JBMTest;