CLR example/ideas to return table from string needing parsing based on tab RRS feed

  • Question

  • Hi guys,

    I'm new to C#, but not new to SQL.  I have a table of about 40 million rows that has a single column.  The single column is TAB delimited values that I have previously BULK INSERTed.  Because the file that I am importing from has 8 different record types identified by the first value in each row, I can not use a field terminator because each record type has different column counts (ranging from 25 columns to 35 columns).

    So my approach has been to bulk insert the row as a single varchar(8000) column then parse the data in sql.  I have written a UDF, but it is pretty slow ( about 4 mins per million records).

    I have been looking at CLR and am struggling to find a good example.  My thought was that I would send a table object (or a row??) to the CLR assembly and have it return a row or table that I then insert?  Also becuase of the data size it seems like I would have to do a data stream to not run out of memory on the server. 

    I was also looking at Adam Mechanic's CLR splitter, as well as the new STRING_SPLIT function but it splits it in rows and I need columns.

    Here are a few links I have looked at:






    And many others..

    Any thoughts / help would be appreciated!


    PS:  I am using SQL Server 2016
    Wednesday, June 20, 2018 2:18 PM

All replies