Write-DataTable

Writes data only to SQL Server tables.

 
 
 
 
 
4.6 Star
(8)
Add to favorites
Databases
10/5/2010
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Why don't I get any errors when I try to run the script with no arguments?
    1 Posts | Last post March 28, 2018
    • If I try:
      
      .\Write-DataTable.ps1
      
      I don't get any errors.  Is that the expected behavior?
  • Support to Fire Triggers with Bulk Insert
    2 Posts | Last post August 17, 2017
    • The following line has been modified with the one beneath to allow triggers on the destination table to fire when data is inserted.
      
      ORIGINAL:
      $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
      
      MODIFIED:
      $bulkCopy = new-object Data.SqlClient.SqlBulkCopy -argumentlist $conn,$([System.Data.SqlClient.SqlBulkCopyOptions]::FireTriggers -bor 0),$null
      
      Cheers!
    • Reuben, thank you so much.  This saved me a ton of headaches and time, thank you!  I think Chad should make part of the regular code.
  • Add port to this script
    1 Posts | Last post August 24, 2016
    • How do I add non default port to this script, as it could be different for each server. Thanks
  • Nonstandard ports and default instances
    1 Posts | Last post October 05, 2015
    • This script (and out-sqltable, add-sqltable) don't seem to play well with a default instance of SQL (\MSSQLServer, aka \Default in SMO). Nor do they work with a non-standard port. Due to the mix of object types used in the scripts, I added the functionality as a separate parameter rather than parsing a ServerInstance that could have a ",portno" in it. You may want to look into updating these cmdlets to account for these scenarios. 
  • question about Numerics with nulls
    1 Posts | Last post May 22, 2013
    • Hi Chad, I had a few numeric columns that were raising exceptions about converting strings to decimals when Nulls existed, even though those columns were nullable.  I ended up just getting rid of the nulls once I realized the issue, but wondering if there was a better way to handle that with Write-DataTable.
      
      thanks, Bruce
  • How to put data into SQL table?
    1 Posts | Last post April 02, 2013
    • I have a PowerShell script parsing data from text files.  I would like to add the details to my SQL database, however, I am not sure how to go from the variables into the database with your code.
      
      Can you help simplify the solution for me to better understand?
      
      Thank you,
      Mike
  • Working with an identity field
    3 Posts | Last post March 02, 2013
    • I tested this and it worked great, until I tried to insert into a table with an identity field as the first column.
      
      Any suggestions?
    • Just as you would with other ETL tools (BCP, SSIS, etc.) you'll need to do a column mapping to handle importing into a table and ignoring rows such as identify columns. The underlying .NET class I'm using SqlBulkCopy supports , supports specifying column mapping, but I didn't implement it in write-datatable. However, the open source project I coordinate, SQLPSX http://sqlpsx.codeplex.com has module called adolib which includes the invoke-sqlbulkcopy function. The function implements column mapping functionality. After installation run import-module adolib and then help invoke-sqlbulkcopy -full for documentation and examples.
    • Clarification -- I meant ignoring columns instead of rows.
  • Type conversion problem
    3 Posts | Last post May 30, 2012
    • I am not having much success with Write-DataTable. I tried generating a DataTable with Out-DataTable...
          $dt = Get-PsDrive | Out-DataTable
      ...then using that to create a properly-typed table...
          Add-SqlTable -Server .\sqlexpress -Database mysandbox -Table psdrives -DataTable $dt
      ... and finally populating the table:
          Write-DataTable -Server .\sqlexpress -Database sandbox -Table psdrives -Data $dt      
      
      But, alas, that yields this error:
      Write-DataTable : System.Management.Automation.MethodInvocationException: Exception calling "WriteToServer" with "1" argument(s): "The given value of type ProviderInfo from the data source cannot be converted to type varchar of the specified target column."
      
      I confirmed that the created table was defined as expected using this command from my SQL Server "drive":
          (gci | ? { $_.name -eq "psdrives" }).script()
      
      The result shows all fields are strings, as expected:
      SET ANSI_NULLS ON
      SET QUOTED_IDENTIFIER ON
      CREATE TABLE [dbo].[psdrives](
      	[Used] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Free] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[CurrentLocation] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Name] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Provider] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Root] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Description] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      	[Credential] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
      ) ON [PRIMARY]
      
      Note that I tried some different cmdlets and observed the same error. Curiously, when I try your Get-WmiObject example from "Use PowerShell to Collect Server Data and Write to SQL" (http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx) that works OK. Is there a "gotcha" embedded in my example?
       
    • The gotcha is understanding the type of the properties of your object and type conversion between .NET types and SQL Server data types. If you look at 
      get-psdrive | get-member -memberType Property
      
      Notice Provider property which is the error message is complaining about is of type ProviderInfo. The Provider column on your datatable $dt has the same type:
      $dt | get-member  -memberType Property
      
      So either you'll need to convert the type some kind of primitive type (int, string, etc.) or use a select-object cmdlet to filter out which properties you want. For this example I thinking using select makes more sense--the Provider property isn't very interesting anyways. He's a reworked script:
      
      $dt = Get-PsDrive | select Currentlocation, Description, Free, Name, Root, Used | Out-DataTable
      Add-SqlTable -Server .\sql1 -Database tempdb -Table psdrives -DataTable $dt
      Write-DataTable -ServerInstance .\sql1 -Database tempdb -TableName psdrives -Data $dt
    • Thanks, Chad, that illuminates it well; I believe I now grok the issue!