tsql : outputting each record to their own text file

Question

is there a simple way to just output each record in a select statement to write to its own file?

for example, if you have the tsql query in sql server 2005,

select top 10 items, names + ':' + address from book 

and you ended up with 10 text files with the individual name and addresses in each file.

is there a way to do this without writing an extensive spWriteStringToFile procedure? I'm hoping there is some kind of output setting or something in the select statement.

thanks in advance

Answer 1

In SSMS, you can do a results to file, but that wouldnt split each record out into its own file. I pretty sure you cannot do this out of the box, so it sounds like you will be rolling your own solution.

Answer 2

You'd do this in some client, be it Java, VBA or SSIS typically.

Answer 3

SQL returns the result set first, there's no opportunity in there for writing records to specific files until afterwards.

Being SQL Server 2005, it's possible you could use a SQLCLR (.NET 2.0 code) function in a SQL statement without having to make a separate application.