Export varbinary(max) data from SQL Server to file system with CLR UDFPosted: January 20, 2016
First, we can create a CLR user-defined function from Visual Studio and deploy the function to SQL Server.
Here is a link to sample code snippet:
In SQL Server we can write a simple recursive CTE block to retrieve all files to be exported. The finl code is to use the CLR-UDF in the T_SQL code.
;with mycte as
select id, parentid, 0 as lvl, path, isfolder,[name], [externalid]
where id = 1
Select s.id, s.parentid, m.lvl+1 as lvl , s.path, s.isfolder, s.[name],s.[externalid]
from samples s join mycte m
select dbo.WriteToFile(n.storagevalue ,N’c:\temp\myFolder’ +path, 0)
from mycte m JOIN [dbo].[dataStores] n on m.externalid=n.externalid
This method is super fast compared to using cursor to execute bcp command line by line.
Here is a nice post about CLR from DBA’s view.