Export files from SQL Server

I don’t recommend storing PDFs in a database, however as a consultant, I come across many things. One of our customers requested that all of the PDFs in their database be exported to disk and burned to a CD.  There are several databases for their customers, so the reports needed to be stored in different file directories.  An additional requirement for the file folders was to separate them by report year.

The bcp command is what is needed to export them.  Since we need to export one file for each PDF, then bcp must be called for each record.

With the goal of avoiding cursors, I just used a WHILE loop.   To be able to loop through all of the needed records, I put them in a new table with an int identity.  More on that later.

–Create the helper table
CREATE TABLE Admin.dbo.ReportExport(
UniqueId intidentity(1,1),
ReportYear int,
OriginalNaturalKey int,
[File] image,
[FileName] nvarchar(100)
)  

–Populate the helper table with all of your files
INSERT Admin.dbo.ReportExport
SELECT year(Reportdate),  NaturalKeyId, [File], [Filename]
FROM ClientReportTable


CREATE UNIQUE CLUSTERED INDEX ix1 on Admin.dbo.ReportExport(UniqueId)
GO 

The WHILE loop will then increment by 1 until it has reached the maximum identity value.

–initialize counter to 1
DECLARE @counter int
SET @counter = 1 

–store maximum value of identity column, so the WHILE knows when to stop
DECLARE @max_value int
SELECT @max_value = MAX(UniqueId) from Admin.dbo.CPReports

DECLARE @filename varchar(2000)
DECLARE @cmd VARCHAR(500)

–begin loop
WHILE
@counter <= @max_value
BEGIN 

–construct file directory name, with year and database name and filename.  The filename is retrieved from the table we made, by matching UniqueId to @counter
SELECT
@filename = ‘f:\ReportExports\’ + CAST(ReportYear as varchar(4)) + ‘\’+ DB_NAME() + ‘\’ + [filename] from Admin.dbo.ReportExport WHERE UniqueId = @counter

–Here’s where we construct the bcp command.  We will tell bcp to query our table where UniqueId = @counter, this time retrieving the Image data type field.  For the QueryOut parameter, use the file name that we stored in the previous step.
SET @cmd = ‘bcp “SELECT [File] FROM Admin.dbo.ReportExport WHERE UniqueID = ‘ + CAST( @counter as varchar(5) )+‘” queryout ‘+@filename+‘ -T -N’;   

–Then finally, we execute the bcp command and move on to the next record

EXEC master..xp_cmdshell@cmd, NO_OUTPUT; 

SET @counter = @counter + 1

END

I was able to export about 350 reports per minute.   But there were hundreds of thousands of reports.  Then it will also take time to move all of the files to a different location to be burned to CD.    So I recommend copying production databases to other environments, and then running the export.

Advertisements
This entry was posted in Database Administration and tagged . Bookmark the permalink.

One Response to Export files from SQL Server

  1. Pingback: Export files from SQL Server using BCP « JustSQL

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