Solomon Rutzky (12/23/2009)
Hey Matt. I would have suggested that for this problem you could have done a CLR Stored Procedure since, as you mentioned, they effectively do stream by returning each row as it is generated. The one thing that I have found that CLR Stored Procs can do that CLR TVFs cannot is return a dynamic result set where the columns aren't known ahead of time and can change between executions. In fact, I have a Stored Proc in my SQL# project that reads a file from disk and splits it into columns using a RegEx delimiter. It is called File_SplitIntoFields. It is not in the Free version but you can see a description of it in the User Manual which is on the Downloads page.Also, another option if you are using SQL Server 2008 is to create a stand-alone .Net app (or include this in part of an existing application) to stream the data from disk into a T-SQL Stored Proc that accepts a Table-Valued Parameter (TVP). My previous article (http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/) shows how to do this :-).
Take care,
Solomon...
The standalone app was in fact the other method. I was more curious "if I could do it" using SQLCLR (was hoping at the time that it might be faster - this was a few years back). The console app ended up being the fastest way to get what I needed done.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?