PoshCode Logo PowerShell Code Repository

Write-DataTable by Chad Miller 3 years ago
View followups from Chad Miller | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2118"></script>download | new post

Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

  1. #######################
  2. <#
  3. .SYNOPSIS
  4. Writes data only to SQL Server tables.
  5. .DESCRIPTION
  6. Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
  7. .INPUTS
  8. None
  9.     You cannot pipe objects to Write-DataTable
  10. .OUTPUTS
  11. None
  12.     Produces no output
  13. .EXAMPLE
  14. $dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select *  from authors"
  15. Write-DataTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -Data $dt
  16. This example loads a variable dt of type DataTable from query and write the datatable to another database
  17. .NOTES
  18. Write-DataTable uses the SqlBulkCopy class see links for additional information on this class.
  19. Version History
  20. v1.0   - Chad Miller - Initial release
  21. .LINK
  22. http://msdn.microsoft.com/en-us/library/30c3y597%28v=VS.90%29.aspx
  23. #>
  24. function Write-DataTable
  25. {
  26.     [CmdletBinding()]
  27.     param(
  28.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  29.     [Parameter(Position=1, Mandatory=$true)] [string]$Database,
  30.     [Parameter(Position=2, Mandatory=$true)] [string]$TableName,
  31.     [Parameter(Position=3, Mandatory=$true)] $Data,
  32.     [Parameter(Position=4, Mandatory=$false)] [string]$Username,
  33.     [Parameter(Position=5, Mandatory=$false)] [string]$Password,
  34.     [Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
  35.     [Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
  36.     [Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
  37.     )
  38.    
  39.     $conn=new-object System.Data.SqlClient.SQLConnection
  40.  
  41.     if ($Username)
  42.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  43.     else
  44.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  45.  
  46.     $conn.ConnectionString=$ConnectionString
  47.  
  48.     try
  49.     {
  50.         $conn.Open()
  51.         $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
  52.         $bulkCopy.DestinationTableName = $tableName
  53.         $bulkCopy.BatchSize = $BatchSize
  54.         $bulkCopy.BulkCopyTimeout = $QueryTimeOut
  55.         $bulkCopy.WriteToServer($Data)
  56.         $conn.Close()
  57.     }
  58.     catch
  59.     {
  60.         $ex = $_.Exception
  61.         Write-Error "Write-DataTable  $($connectionName):$ex.Message"
  62.         continue
  63.     }
  64.  
  65. } #Write-DataTable

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