dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Write-DbaDataTable

Quickly and efficiently writes data to a SQL Server Table using a .NET DataTable to a SQL Server table using SQL Bulk Copy.

Examples

Quickly and efficiently performs a bulk insert of all the data in customers.csv into database: mydb, schema: dbo, table: customers. Shows progress as rows are inserted. If table does not exist, import is halted.

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers

Performs row by row insert. Super slow. No progress bar. Don’t do this. Use -InputObject instead.

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
$datatable | Write-DbaDataTable -SqlServer sql2014 -Table mydb.dbo.customers

Quickly and efficiently performs a bulk insert of all the data. If mydb.dbo.customers does not exist, it will be created with inefficient but forgiving datatypes.

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers -AutoCreateTable

Quickly and efficiently performs a bulk insert of all the data. Prompts to confirm that truncating mydb.dbo.customers prior to import is desired.
Prompts again to perform the import. Answer A for Yes to All.

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers -Truncate

Quickly and efficiently performs a bulk insert of all the data into mydb.dbo.customers — since Schema was not specified, dbo was used.

Per Microsoft, KeepNulls will “Preserve null values in the destination table regardless of the settings for default values. When not specified, null values are replaced by default values where applicable.”

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Database mydb -Table customers -KeepNulls

Get-Help

From PowerShell, execute Get-Help Write-DbaDataTable -Detailed for more information on this function.

Source Code

Want to see the source code? View Write-DbaDataTable.ps1 on GitHub
 

Related commands