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

more ▼

asked Aug 22, 2012 at 01:17 PM in Default

mike_spencer gravatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)
more ▼

answered Aug 23, 2012 at 05:22 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 23, 2012 at 05:08 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 22, 2012 at 01:17 PM

Seen: 2895 times

Last Updated: Aug 23, 2012 at 05:08 PM