Can I write output to flat files through stored procedures?
I have written a stored procedure which a) fetches rows in a loop from a table, b) perform complex calculation on those set of rows and c) finally updates/inserts those rows into a table.
It was all working fine till we got to know that the client doesn't allow using procedure with dynamic sqls. First thing that we could think was to write the output(post calculation) to a flat file and once that is done, use some utility to push those records into the final table. Is this the best way to do? If so then how can we write the output to a flat file?
Re: Can I write output to flat files through stored procedures?
It's generally recommended to do all the processing within the TD system, if the target is the same TD system.
Depending on the nature of your processing logic, it may be possible to write a complex SQL to do it, use a UDF, do some bteq "tricks" to generate and execute SQL dynamically etc ....or a design something using a combination of all that gimmicks !