lets say I'm trying to run the following .sql file from powershell
SELECT * FROM table
Currently this is how I am calling this file
Invoke-Sqlcmd -ServerInstance instance -Database database -Username username -Password password -InputFile basicQuery.sql
If I wanted to select from "myTable" how would I pass that parameter to the sql file?
If the query is as basic as in your example, it would be better to replace the -InputFile parameter with -Query, where you easily can use a variable. E.g.
$tables = "MyTable1","MyTable2"
$tables | Foreach-Object {
Invoke-Sqlcmd -ServerInstance instance -Database database -Username username -Password password -Query "SELECT * FROM $_"
}
For more advanced queries, you could do a file search replace:
Get-Content sqlTemplate.sql | Foreach-Object {
$_ -replace "TABLE", "MyTable" | Out-File myQuery.sql -Append
}
Invoke-Sqlcmd -ServerInstance instance -Database database -Username username -Password password -InputFile myQuery.sql
