Exporting multiple TSQL result sets into one spreadsheet using Powershell

Question

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table names as parameters. The procedures are for a data validation process to see what is not aligned to our specifications. These all check for different things: the "validity" of specific values, formatting etc. That said they all have a different output, including the number of columns. I am attempting to automate the load, the stored procedure runs, and the placement of the result sets into a spreadsheet.

I have written a Powershell script to load the files into the DB using dbatools, and then run the "root" stored procedure that runs the other 12 or so. I have found myself a bit stuck attempting to export this data without doing 12+ separate stored procedure calls into separate CSVs or inserting the results into a "final" table that includes header names in the rows... I will do that if needed, but this output is typically sent to clients for review; forcing them to open up to 12 CSVs for a review is cumbersome and having one table is not very clean given how much the columns differ.

TSQL is my jam and I am not super experienced with PS. If I am being dumb, please point me to a relevant resource that I can make myself not dumb. Any suggestions are appreciated. Thanks

Answer 1

There are two approaches you could take:

1. Use the UNION operator in SQL to combine the output of the 12 procedures into a single view.
2. Write twelve different CSV files and use PS to merge them.

Without the particulars of your case, it's hard to know which approach is more promising. - user1050