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(
–Populate the helper table with all of your files
SELECT year(Reportdate), NaturalKeyId, [File], [Filename]
CREATE UNIQUE CLUSTERED INDEX ix1 on Admin.dbo.ReportExport(UniqueId)
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)
WHILE @counter <= @max_value
–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
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.