sql server - Run SQL Script within a table/string and output results to file -

i need run sql scripts held in report configuration table , output results .csv or .xls files in desired folder file name specified within report config table.

an example of table per script below column being desired file name created , column b contains script executed.

ideally require script can drop stored proc run down each row in table , export , create each rows results separate file in desired folder.

i think needs recursive query i'm not sure start, advice or appreciated.

edit: suggested use ssis, can done existing toolbox items or need write script this? there hundreds of sql scripts in table efficiency key!

set ansi_nulls on go set quoted_identifier on go create table [dbo].[test_table_ps](   [file_name] [nvarchar](100) null,   [script_to_run] [nvarchar](100) null ) on [primary] go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_1', n'select * fred') go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_2', n'select * bob') go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_3', n'select * dave') go 

you'd best using while loop or cursor operation. although while loop requires id field. this:

set ansi_nulls on go set quoted_identifier on go create table [dbo].[test_table_ps](   [file_name] [nvarchar](100) null,   [script_to_run] [nvarchar](100) null,   [report_id] int identity(1,1) ) on [primary] go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_1', n'select * fred') go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_2', n'select * bob') go  insert [dbo].[test_table_ps] ([file_name], [script_to_run])  values (n'report_3', n'select * dave') go  declare @sql nvarchar(max) declare @script nvarchar(max) declare @filename nvarchar(50) declare @id int  set @id = 1  while @id <= (select max(report_id) [dbo].[test_table_ps])     begin         select @script = script_to_run [dbo].[test_table_ps] @id = report_id         select @filename = filename [dbo].[test_table_ps] @id = report_id          set @sql = 'bcp "'+@script+'" queryout "'+@filename+'" -t -c -t,'         print @sql         exec (@sql)          set @id = @id + 1     end 

don't forget add filepath filenames.