sqllover999 sqllover999 - 4 months ago 10
SQL Question

Create mulitple csv automatically from different select statements & save in a given path

I have a store proc to give csv output in a path after the query executes.But it only works for a single csv.

I want a script to generate multiple csv together from multiple sql statment and store in a path.

My input is here

------------------------------------------
--- Generating output csv by store proc ---
---------------------------------------------



DECLARE @sql NVARCHAR(MAX) = 'SELECT * from #TMP_Lazyone_All_Final'
DECLARE @file_path NVARCHAR(4000) = N'D:\a\Lazyone_ComplexProducts_Final.csv' -- Change filename path
DECLARE @Status INT = 1

EXECUTE spa_util_GetCsv @DBFetch = @sql,
@Header = 1,
@PCWrite = @file_path,
@HeaderQuote = 0,
@Status = @Status OUTPUT

Answer

This can be achieved using Shell Script as below.

$categories = Invoke-SqlCmd -ServerInstance SERVERINSTANCENAME -Database DATABASENAME -Query "use yourDBName;select categories from yourtable;"
  foreach($category in $categories){
     $categoryName = $category.name
     $attachmentFilePath = "D:\$categoryName.csv"
     $QueryFmt = "Select * from yourtable where categories=$categoryName"
     Invoke-Sqlcmd -ServerInstance SERVERINSTANCENAME -Database DATABASENAME  -Query $QueryFmt | Export-CSV $attachmentFilePath
  }