PowerShell is a very talented command line that also provides the ability to connect and query SQL servers. We can use the Invoke-Sqlcmd command in order to query SQL servers. The Invoke-Sqlcmd command provides lots of features like a query from a file, passing the variables to the query, etc.
Run SQL Query
We can simply run the Invoke-Sqlquery in order to run SQL queries on SQL servers. The SQL query is specified with the -Query parameter and the -ServerInstance is used to specify the SQL Server instance. In the following example, we connect the database server named DBServer and use the database named Customers.
PS> Invoke-Sqlcmd -Query "SELECT * FROM Users" -ServerInstance "DBServer\Customers"Run SQL Query File
Sometimes the SQL Query may be saved into a file. We can use this file to run SQL queries. In the following example, we run the SQL query located inside the file named “myquery.sql”. The SQL query file is specified with the -InputFile parameter.
PS> Invoke-Sqlcmd -InputFile myquery.sql -ServerInstance "DBServer\Customers"Pass PowerShell Variables to SQL Query
Another useful feature of the Invoke-Sqlquery is the ability to provide PowerShell variables or PowerShell arrays into the SQL query as parameters. The -Variable is used to specify the Array name and the array item keys are used inside the SQL query.
PS> $Names = "KEY1='VAL1'","KEY2='VAL2'"
PS> Invoke-Sqlcmd -Query "SELECT * FROM Users WHERE UserName=`$(KEY1) SurName=`$(KEY2)" -ServerInstance "DBServer\Customers"