Performing bulk export using Invoke-SqlCmd
This recipe shows how to export contents of a table to a CSV file using PowerShell and the Invoke-SqlCmd cmdlet.
Getting ready
Make sure you have access to the AdventureWorks2014 database. We will use the Person.Person table. Create a C:\Temp folder, if you don't already have it in your system.
How to do it...
Follow these steps to perform a bulk export using PowerShell and Invoke-sqlcmd:
Open PowerShell ISE as administrator.
Import the SQLPS module and create a new SMO Server Object:
#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName = "localhost" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Add the following script and run:
#database handle $dbName = "AdventureWorks2014" $db = $server.Databases[$dbName] #export file name $exportfile = "C:\Temp\Person_Person.csv" $query = @" SELECT * FROM Person.Person "@ Invoke-Sqlcmd -Query...