Powershell invoke-sqlcmd from query in a txt file

Question

I have TSQL insert statements in a multiple text file in a folder. I need to run on sql server using foreach loop in powershell. it should go to each file in a folder read the query execute that and terminate the connection again read the next file and do the same operation

invoke-sqlcmd -ServerInstance KUMSUSHI7 -Query (Get-Content | For-each "C:\Drill\Task\SAMS Automation\Query.txt")

Please help me on this

Answer 1

Invoke-Sqlcmd can take script file as a parameter, so no -Query is needed. There's an example on MSDN:

Invoke-Sqlcmd -InputFile "C:\TestSQLCmd.sql" | Out-File -filePath "C:\TestSQLCmd.rpt"

Thus, get a list of your sql files and pass the file names to Invoke-Sqlcmd like so,

gci "c:\some\path\*" -include *.sql | % {
    Invoke-Sqlcmd -InputFile $_.FullName
}