Export varbinary(max) data from SQL Server to file system with CLR UDF


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:

http://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

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]
from samples
where id = 1
–95833
Union all
Select s.id, s.parentid, m.lvl+1 as lvl , s.path, s.isfolder, s.[name],s.[externalid]
from samples s join mycte m
on s.parentid=m.id

)

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.

http://www.sqlconsulting.com/news1009.htm

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s