Export to seperates text file for each record

Question

Export to seperates text file for each record

Manchu Mike

SSC Enthusiast

Points: 167
More actions
February 8, 2010 at 10:43 am

#215393

Hi, I'm looking to export for each record in select statment to seperate text files. The text file names would be in each record. The records would then be updated to reflect that text file was made. The format of the file would be EDI type, meaning each column would be on a seperate line with some static data. See example.
ST|856|sqldatahere~
BSN|00|892345-576|sqldatahere|0830~
DTM|011|sqldatahere|0830~
Could this be done using streamwriter in a stored procedure? I'm kind of new to this and am looking for a general direction or perhaps some samples if possible.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Januario SSChasing Mays Points: 609 More actions · Answer 1

It seems to me that doing it from within the stored procedure isn't the best option. I think there are security issues with giving write access to the file system from within sql server like that. Integration Services (DTS) has functionality to do things like that, where you can read the data from the table, loop through each record, and then use a vb script to output to a text file. Since the values from each row in the table can be made available to the script, you can read in the filename, create the text file, output the rest of the data in the format that you are looking for, close the text file, and then repeat until you get to the end of your recordset. I think that would be a much better way to do this.

Is that kink of on the line that you were looking for?

Manchu Mike SSC Enthusiast Points: 167 More actions · Answer 2

Thanks for the reply Tim!

Actually I did go one of those other routes for this through a VBScript and it works really good. Now I'm trying to find a way to run the script whenever the values are updated. Currently I have it set on a schedule in a SQL job but I would rather it only run when needed. Any suggestions?

Thanks

Bill Nicolich SSCommitted Points: 1865 More actions · Answer 3

Hey there.

I don't have a solution to hand over. But maybe I can help the thought process along.

There are a couple of ways to respond to events. Triggers on a table is an accessible way to respond to insert/update/delete events. You might check there first and see if it's feasible and reasonable.

Then there's the service broker service - where you have the ability to respond to events and create events. Check out part this MSDN section real quick for CREATE EVENT NOTIFICATION. This is sort of a deep-dive area. But you might want to check into it a little to decide if you want to go there now or maybe later. Check out An Introduction to SQL Server Service Broker.

Also, kindly check back in and report on what you did or learned for the benefit of other community members! Thanks.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow

Manchu Mike SSC Enthusiast Points: 167 More actions · Answer 4

Thanks for the reply Bill. I will check these out and keep the post updated.