Working with SqlBulkCopy can sometimes be a struggle. Sure, if you happen to get the data from a table that is identical to the one you want to push to, you are in luck. If not, you need to be sure that the DataTable object is exactly like the target table in the SQL database you want to import data to. One type wrong, or one column in the wrong place, and you are out of luck… it won’t work.

This function can save you some frustration. It will connect to an already existing table and create an empty DataTable ready to fill with data! Nice, eh?

function New-DataTableFromSqlTable{
Create a new (empty) DataTable object based on a SQL Table.
Create a new (empty) DataTable object based on a SQL Table.
.PARAMETER DatabaseServer
The Hostname of the database server.
.PARAMETER DatabaseName
The name of the database
The name of the table you wish to create as an empty DataTable object.
$dataTable = New-DataTableFromSqlTable -DatabaseServer DBServer01 -DatabaseName DB01 -TableName Table01
Will connect to DBServer01/DB01 and create a new DataTable object based on Table01 and save to $dataTable.
Name: New-DataTableFromSqlTable
Author: Øyvind Kallstad
Date: 18.02.2014
Version: 1.0
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
# connect to database server
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$dbSrv = New-Object Microsoft.SqlServer.Management.SMO.Server $DatabaseServer
# get the database
$db = New-Object Microsoft.SqlServer.Management.SMO.Database
$db = $dbSrv.Databases[$DatabaseName]
# get the table
$table = New-Object Microsoft.SqlServer.Management.SMO.Table
$table = $db.Tables[$TableName]
# get table columns
$columns = New-Object Microsoft.SqlServer.Management.SMO.Column
$columns = $table.Columns
# create a datatable – this will be our returned object
$dt = New-Object System.Data.DataTable($TableName)
# an array to hold primary keys
$keys = @()
# iterate through each column
foreach ($column in $columns){
# convert SQL datatypes to .NET datatypes
switch ($column.DataType.Name){
'tinyint' {$dataType = 'Byte'}
'binary' {$dataType = 'Byte[]'}
'varbinary' {$dataType = 'Byte[]'}
'image' {$dataType = 'Byte[]'}
'timestamp' {$dataType = 'Byte[]'}
'bit' {$dataType = 'Boolean'}
'smallint' {$dataType = 'Int16'}
'int' {$dataType = 'Int32'}
'bigint' {$dataType = 'Int64'}
'real' {$dataType = 'Single'}
'float' {$dataType = 'Double'}
'decimal' {$dataType = 'Decimal'}
'numeric' {$dataType = 'Decimal'}
'money' {$dataType = 'Decimal'}
'smallmoney' {$dataType = 'Decimal'}
'time' {$dataType = 'TimeSpan'}
'date' {$dataType = 'DateTime'}
'datetime' {$dataType = 'DateTime'}
'datetime2' {$dataType = 'DateTime'}
'smalldatetime' {$dataType = 'DateTime'}
'xml' {$dataType = 'Xml'}
'nchar' {$dataType = 'String'}
'char' {$dataType = 'String'}
'nvarchar' {$dataType = 'String'}
'varchar' {$dataType = 'String'}
'ntext' {$dataType = 'String'}
'text' {$dataType = 'String'}
DEFAULT {$dataType = 'string'}
# create a datacolumn object
$dtColumn = New-Object System.Data.DataColumn ($column.Name), ($dataType)
$dtColumn.AllowDBNull = $column.Nullable
if ($column.Identity){
$dtColumn.AutoIncrement = $true
$dtColumn.AutoIncrementSeed = $column.IdentitySeed
$dtColumn.AutoIncrementStep = $column.IdentityIncrement
# collect keys
if ($column.InPrimaryKey){
$keys += $dtColumn
# add the column to the datatable
# add primary keys
$dt.PrimaryKey = $keys
# catching exceptions
if ($_.Exception.InnerException){
Write-Warning $_.Exception.InnerException.Message
Write-Warning $_.Exception.Message
# the finished datatable object
Write-Output @(,($dt))

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s