PowerShell – Saving SQL query results to a CSV file

With PowerShell, you can execute a SQL query with Invoke-Sqlcmd. If you want to save the query results to a CSV file, you can use Export-Csv. Here’s an example:

$query = "SELECT * FROM TODO.dbo.NFLTeams"

$data = Invoke-Sqlcmd -ServerInstance "SqlServerInstance" -Query $query
                    
$data | Export-Csv -Append -Path "C:\Temp\NFLTeamStats.csv" -NoTypeInformation
Code language: PowerShell (powershell)

If Invoke-SqlCmd is missing, install the SqlServer module

If it’s complaining about not having Invoke-SqlCmd available, you will need to install the SQL Server PowerShell module.

1 – Run PowerShell as administrator

2 – Add Microsoft’s PSGallery as a trusted package source

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Code language: PowerShell (powershell)

Note: This is to avoid the annoyance of being prompted every time you run Install-Module.

3 – Install SqlServer module:

Install-Module -Name SqlServer -AllowClobber
Code language: PowerShell (powershell)

Note: The -AllowClobber flag overwrites the existing module if an older version is already installed.