I have a list of queries to use for an array of associated output files. I'm having a block as to what a good design would be to put it all together for my queries to be executed and the output sent to the files in a smooth way. Usually I don't have the same thing that needs doing so many times. This is with powershell but I should be able to use most design ideas given. It looks like this:

$SQLquery_1 = @" blah "@

$SQLquery_2 = @" blah "@

#There's maybe 10 different queries

#The output files
$extractFiles = @("C:\temp\ProfileLongLangBak.csv","C:\temp\ProfileLongCredBak.csv", blah blah list of files)

#and of course there's about 10 powershell commands to do the sql query which works just fine. The below hasn't incorporated separate sql queries and output files yet

#create sql connection to connect to the sql DB
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=sqldev;Database=M_Test;User ID=blah;Password=blah" 

#Create a SqlCommand object to define the query
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandText = $SQLquery_Languages
$sqlCmd.Connection = $sqlConnection

#create a SqlAdapter that actually does the work and manages everything
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd

#create an empty dataSet for the query to fill with its results
$dataSet = New-Object System.Data.DataSet

#execute the query and fill the dataSet (then disconnect)

#dump the data to csv
$DataSet.Tables[0] | Export-Csv $extractFile #this may not be comma delimited...need to check

My first thought is to have the sql queries in an array and loop through both arrays with a for loop starting at $i=0. But the queries are too long to have in an array.

I was thinking of putting all the powershell commands for the query in a function and call the function with each query and output file as input and with each call another file of results would be written.

Does anyone have a good idea how to handle the long query strings in a smooth design to go through the long list of queries and output files? Thanks!

  • What do you mean by "But the queries are too long to have in an array"? – Doc Brown Dec 17 '18 at 16:11
  • Each SQL Query is probably 20-100 lines long. I don't think I can store them directly in an array without having the array look really messy. – Michele Dec 17 '18 at 16:16
  • 1
    Well, somewhere you have to store them, correct? Where will they look less messy, what do you think? – Doc Brown Dec 17 '18 at 16:21
  • 1
    100 lines of SQL is going to look like 100 lines of SQL no matter how or where you store it. – Robert Harvey Dec 17 '18 at 16:23
  • I'm thinking of having an array of my sql queries, and use the string name to store them in that array. Then it will look neat and each one will be stored with the string name. Like $SQLqueries = @($SQLquery_1, $SQLquery_2, ...). That might do the trick. – Michele Dec 17 '18 at 16:26

I wound up doing this:

$SQLquery_Lang = @"

$SQLquery_Cred = @"
 SELECT blah

$SQLqueries = @($SQLquery_Lang, $SQLquery_Cred,   blah ... )
$extractFiles = @("C:\temp\ProfileLongLangBak.csv","C:\temp\ProfileLongCredBak.csv", blah ...)

#Loop through list of files and queries for ProfileLong
for($i=0; $i -lt ($SQLqueries.Length); $i++) {
   SQLQueryWriteToFile $SQLqueries[$i] $extractFiles[$i] #function call to process and write to file


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.