PoshCode Logo PowerShell Code Repository

Add-SqlTable by Chad Miller 17 months ago
View followups from Chad Miller | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2120"></script>download | new post

Creates a SQL Server table from a DataTable using SMO.

  1. try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
  2. catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"}
  3.  
  4. #######################
  5. function Get-SqlType
  6. {
  7.     param([string]$TypeName)
  8.  
  9.     switch ($TypeName)
  10.     {
  11.         'Boolean' {[Data.SqlDbType]::Bit}
  12.         'Byte[]' {[Data.SqlDbType]::VarBinary}
  13.         'Byte'  {[Data.SQLDbType]::VarBinary}
  14.         'Datetime'  {[Data.SQLDbType]::DateTime}
  15.         'Decimal' {[Data.SqlDbType]::Decimal}
  16.         'Double' {[Data.SqlDbType]::Float}
  17.         'Guid' {[Data.SqlDbType]::UniqueIdentifier}
  18.         'Int16'  {[Data.SQLDbType]::SmallInt}
  19.         'Int32'  {[Data.SQLDbType]::Int}
  20.         'Int64' {[Data.SqlDbType]::BigInt}
  21.         default {[Data.SqlDbType]::VarChar}
  22.     }
  23.        
  24. } #Get-SqlType
  25.  
  26. #######################
  27. <#
  28. .SYNOPSIS
  29. Creates a SQL Server from a DataTable
  30. .DESCRIPTION
  31. Creates a SQL Server from a DataTable using SMO.
  32. .EXAMPLE
  33. $dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select *  from authors"; Add-SqlTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -DataTable $dt
  34. This example loads a variable dt of type DataTable from query and creates an empty SQL Server table
  35. .EXAMPLE
  36. $dt = Get-Alias | Out-DataTable; Add-SqlTable -ServerInstance "Z003\R2" -Database pubscopy -TableName alias -DataTable $dt
  37. This example creates a DataTable from the properties of Get-Alias and creates an empty SQL Server table.
  38. .NOTES
  39. Add-SqlTable uses SQL Server Management Objects (SMO). SMO is installed with SQL Server Management Studio and is available
  40. as separate download: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52
  41. Version History
  42. v1.0   - Chad Miller - Initial Release
  43. #>
  44. function Add-SqlTable
  45. {
  46.  
  47.     [CmdletBinding()]
  48.     param(
  49.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  50.     [Parameter(Position=1, Mandatory=$true)] [string]$Database,
  51.     [Parameter(Position=2, Mandatory=$true)] [String]$TableName,
  52.     [Parameter(Position=3, Mandatory=$true)] [System.Data.DataTable]$DataTable,
  53.     [Parameter(Position=4, Mandatory=$false)] [string]$Username,
  54.     [Parameter(Position=5, Mandatory=$false)] [string]$Password,
  55.     [ValidateRange(1,8000)]
  56.     [Parameter(Position=6, Mandatory=$false)] [Int32]$MaxLength=1000
  57.     )
  58.  
  59.     if($Username)
  60.     { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance,$Username,$Password }
  61.     else
  62.     { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $ServerInstance }
  63.        
  64.     $con.Connect()
  65.  
  66.     $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con
  67.     $db = $server.Databases[$Database]
  68.     $table = new-object ("Microsoft.SqlServer.Management.Smo.Table") $db, $TableName
  69.  
  70.     foreach ($column in $DataTable.Columns)
  71.     {
  72.         $sqlDbType = [Microsoft.SqlServer.Management.Smo.SqlDataType]"$(Get-SqlType $column.DataType.Name)"
  73.         if ($sqlDbType -eq 'VarBinary' -or $sqlDbType -eq 'VarChar')
  74.         { $dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType, $MaxLength }
  75.         else
  76.         { $dataType = new-object ("Microsoft.SqlServer.Management.Smo.DataType") $sqlDbType }
  77.         $col = new-object ("Microsoft.SqlServer.Management.Smo.Column") $table, $column.ColumnName, $dataType
  78.         $col.Nullable = $column.AllowDBNull
  79.         $table.Columns.Add($col)
  80.     }
  81.  
  82.     $table.Create()
  83.  
  84. } #Add-SqlTable

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me