PoshCode Archive  Artifact [26f8c8a001]

Artifact 26f8c8a0016b63dc26f879cad6b7b31c548eb7e40552acb4277d74a328e66f92:

• File Add-SqlTable.ps1 — part of check-in [894c3acc1d] at 2018-06-10 14:02:20 on branch trunk — Creates a SQL Server table from a DataTable using SMO. (user: Chad Miller size: 5943)

# encoding: ascii
# api: powershell
# title: Add-SqlTable
# description: Creates a SQL Server table from a DataTable using SMO.
# version: 1.0
# type: function
# author: Chad Miller
# license: CC0
# function: Get-SqlType
# x-poshcode-id: 5834
# x-archived: 2015-04-24T18:17:31
# x-published: 2015-04-19T12:53:00
#
#
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop} 
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"} 
 
try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}  
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"}  
 
#######################  
function Get-SqlType  
{  
    param([string]$TypeName)  
  
    switch ($TypeName)   
    {  
        'Boolean' {[Data.SqlDbType]::Bit}  
        'Byte[]' {[Data.SqlDbType]::VarBinary}  
        'Byte'  {[Data.SQLDbType]::VarBinary}  
        'Datetime'  {[Data.SQLDbType]::DateTime}  
        'Decimal' {[Data.SqlDbType]::Decimal}  
        'Double' {[Data.SqlDbType]::Float}  
        'Guid' {[Data.SqlDbType]::UniqueIdentifier}  
        'Int16'  {[Data.SQLDbType]::SmallInt}  
        'Int32'  {[Data.SQLDbType]::Int}  
        'Int64' {[Data.SqlDbType]::BigInt}  
        'UInt16'  {[Data.SQLDbType]::SmallInt}  
        'UInt32'  {[Data.SQLDbType]::Int}  
        'UInt64' {[Data.SqlDbType]::BigInt}  
        'Single' {[Data.SqlDbType]::Decimal} 
        default {[Data.SqlDbType]::VarChar}  
    }  
      
} #Get-SqlType 
 
#######################  
<#  
.SYNOPSIS  
Creates a SQL Server table from a DataTable  
.DESCRIPTION  
Creates a SQL Server table from a DataTable using SMO.  
.EXAMPLE  
$dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select *  from authors"; Add-SqlTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -DataTable $dt  
This example loads a variable dt of type DataTable from a query and creates an empty SQL Server table  
.EXAMPLE  
$dt = Get-Alias | Out-DataTable; Add-SqlTable -ServerInstance "Z003\R2" -Database pubscopy -TableName alias -DataTable $dt  
This example creates a DataTable from the properties of Get-Alias and creates an empty SQL Server table.
.EXAMPLE 
Add-SqlTable -ServerInstance "Z003\R2" -Database pubs -TableName alias -DataTable $dt -Schema data
This example creates a table from a variable dt of type DataTable and creates an empty SQL Server table in the 'data' schema  
.NOTES  
Add-SqlTable uses SQL Server Management Objects (SMO). SMO is installed with SQL Server Management Studio and is available  
as a separate download: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52  
Version History  
v1.0   - Chad Miller - Initial Release  
v1.1   - Chad Miller - Updated documentation 
v1.2   - Chad Miller - Add loading Microsoft.SqlServer.ConnectionInfo 
v1.3   - Chad Miller - Added error handling 
v1.4   - Chad Miller - Add VarCharMax and VarBinaryMax handling 
v1.5   - Chad Miller - Added AsScript switch to output script instead of creating table 
v1.6   - Chad Miller - Updated Get-SqlType types 
v1.7   - M. Buckley  - Added a Schema Parameter
#>  
function Add-SqlTable  
{  
  
    [CmdletBinding()]  
    param(  
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,  
    [Parameter(Position=1, Mandatory=$true)] [string]$Database,  
    [Parameter(Position=2, Mandatory=$true)] [String]$TableName,  
    [Parameter(Position=3, Mandatory=$false)] [String]$Schema,  
    [Parameter(Position=4, Mandatory=$true)] [System.Data.DataTable]$DataTable,  
    [Parameter(Position=5, Mandatory=$false)] [string]$Username,  
    [Parameter(Position=6, Mandatory=$false)] [string]$Password,  
    [ValidateRange(0,8000)]  
    [Parameter(Position=7, Mandatory=$false)] [Int32]$MaxLength=1000, 
    [Parameter(Position=8, Mandatory=$false)] [switch]$AsScript 
    )  
  
 try { 
    if($Username)  
    { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance,$Username,$Password }  
    else  
    { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance }  
      
    $con.Connect()  
  
    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con  
    $db = $server.Databases[$Database]  
    if ($schema) 
		{$table = new-object ("Microsoft.SqlServer.Management.Smo.Table") $db, $TableName  , $Schema}
	else
		{$table = new-object ("Microsoft.SqlServer.Management.Smo.Table") $db, $TableName}
  
    foreach ($column in $DataTable.Columns)  
    {  
        $sqlDbType = [Microsoft.SqlServer.Management.Smo.SqlDataType]"$(Get-SqlType $column.DataType.Name)"  
        if ($sqlDbType -eq 'VarBinary' -or $sqlDbType -eq 'VarChar')  
        {  
            if ($MaxLength -gt 0)  
            {$dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType, $MaxLength} 
            else 
            { $sqlDbType  = [Microsoft.SqlServer.Management.Smo.SqlDataType]"$(Get-SqlType $column.DataType.Name)Max" 
              $dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType 
            } 
        }  
        else  
        { $dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType }  
        $col = new-object ("Microsoft.SqlServer.Management.Smo.Column") $table, $column.ColumnName, $dataType  
        $col.Nullable = $column.AllowDBNull  
        $table.Columns.Add($col)  
    }  
  
    if ($AsScript) { 
        $table.Script() 
    } 
    else { 
        $table.Create() 
    } 
} 
catch { 
    $message = $_.Exception.GetBaseException().Message 
    Write-Error $message 
} 
   
} #Add-SqlTable