Use Powershell To Execute SQL Select Query

Using Powershell we can connect to and return a simple T-SQL query result set using your existing SQL code. We are using the invoke-sqlcmd commandlet for this example so I included the Powershell command to install. (you may need to start VSCode as Administrator).

# Install-Module SQLServer
# Import-Module -Name SQLServer

$myquery = "SELECT column1, column2, datecreated FROM [DBName].dbo.[TableName]"
$resultdata = invoke-sqlcmd -ServerInstance sqlserver\instance -Query $myquery

# $resultsarray | Out-GridView
# $resultsarray.column2
$resultsarray | Format-Table

The $resultdata variable will contain the same query results you see in SSMS and we can pipe the results to a gridview, table or Select/Where statement to filter.
Be sure to only have 1 set of results from your query or you can see strange results in the array.

Select Query Where Clause Example

The object type returned from our Invoke-sqlcmd commandlet is a ‘System.Array’ and you will notice that the SQL statement column name is available. We can use this to filter the result set in a where statement on the pipeline.

$resultsarray | Where-Object {$_.column1 -eq “somestring”}
$resultsarray | Where-Object {$_.column2 -like “partname*”}
$resultsarray | Where-Object {$_.column1 -gt 34 -and $_.column2 -like “string*” }

Select Query Foreach Example

We can also use a foreach command to work with only 1 record at a time. In this example foreach is assigning (db tales com) the next record to the $result variable and we can work with it inside the curly brackets {}.

The foreach is where we can make decisions based on a single records values. In this example we are exploring the foreach and utilizing an IF statement to store values to a $secondarray, if the value satisfies our criteria.

$secondarray = @()  ## create array

Foreach($result in $resultsarray ) {
    write-host $result| Select-Object -Property $_.column2
    write-host $result.column1
    
    if($result.column1 -eq "myvalue") {
        $secondarray += $result.column1  ## add value to array
    }
}