PowerShell for Execute SQL Query
Invoke-Sqlcmd cmdlet which is all-purpose SQL utility cmdlet being used in PowerShell. As the name suggests, Invoke-Sqlcmd cmdlet allows you to run T-SQL code or scripts and commands supported by the SQLCMD utility. It also allows you to run XQuery code. In this article, we will be looking at two ways of using Invoke-Sqlcmd.
The first is by specifying a SQL File Name and exporting results to CSV file
#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
#provide your database name where you want to execute your SQL File
$DatabaseName = "AdventureWorks2008R2"
#Invoke SQL to execute SQL File and export to CSV
Invoke-Sqlcmd `
-InputFile "C:\Temp\SQLScript.sql" `
-ServerInstance $SQLInstanceName `
-Database $DatabaseName |
Export-CSV -LiteralPath "C:\Temp\PowerShellRessults.csv" `
-NoTypeInformation
The second is by providing inline SQL Query and view the results.
#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
#provide your database name where you want to execute your SQL Script
$DatabaseName = "AdventureWorks2008R2"
#Invoke SQL to execute SQL File and export to CSV
Invoke-Sqlcmd `
-Query "SELECT ProductID, Name, ProductNumber, Color, Size FROM Production.Product" `
-ServerInstance $SQLInstanceName `
-Database $DatabaseName |
Select ProductID, Name, ProductNumber, Color, Size |
Format-Table
Hope this helps
Sandip
Leave a Reply
BI Big Data Authors
Sandip Shinde
Manish Kaushik
Suhas Kudekar(MCC)
Umesh M Patil
YashwantOur Visitors
Big Data, Data Analytics and Data Science | BI-BIGDATA.com 
It’s really a great and helpful piece of information. I’m glad that you shared this helpful information with us. Please keep us informed like this. Thanks for sharing
I really love this site. You write about very interesting things. Thanks for all your tips and information.
Heya i am for the first time here. I found this board and I find It really useful & it helped me out much. I hope to give something back and help others like you helped me.
Good day! This post could not be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Fairly certain he will have a good read. Many thanks for sharing!
Can I simply say what a reduction to find somebody who truly is aware of what theyre speaking about on the internet. You definitely know the way to carry an issue to gentle and make it important. Extra people have to learn this and understand this aspect of the story. I cant imagine youre not more in style since you definitely have the gift.