How can I export one column as a separate text file for each row?

Let's say I have a table with this info in SQL Server 2008 (2 columns):

DocNumber, Text

0000001, this is the text for doc 1

0000002, this is the text for doc 2

What I need to do, is create text files for each row named by the DocNumber column.

So something like select [Text] from table and then send that to [DocNumber].txt, then loop through the rows. I'm guessing I'll need to use xp_cmdshell.

I need to end up with 000001.txt and 000002.txt.

Any suggestions or resources that might have something similar that I can play around with? It just needs to be a SQL script.

thanks, Mike

asked Aug 22 '12 at 01:17 PM in Default

mike_spencer
0 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

If it has to be a SQL Script, you will need to utilize some OLE OBjects to extract the data.

Anyway, probably the easiest wayt to export the data would be using a SSIS package. You can use the Export Column data transformation to store a value of particular field into a file. It can handle varchar(max), nvarchar(max) and varbinary(max) fields. equivalent (DT_TEXT, DT_NTEXT, DT_IMAGGE) data types in SSIS. Even wahtever varchar field is possible to extract by doing simple conversion in the package or in the query to the varchar(max) or nvarchar(max)

answered Aug 23 '12 at 05:22 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

Pavel is completely right. But I came over this: http://www.mssqltips.com/sqlservertip/2693/export-images-from-a-sql-server-table-to-a-folder-with-ssis/ yesterday which I think will be helpful. That particular example is about exporting image files, but it would take only small tweaks to make it work with text files.

answered Aug 23 '12 at 05:08 PM