Search code examples
sql-serverpowershellsqlcmdtruncated

Powershell sql insert error data will be truncated removed data and still get error

I have a powershell script that I am trying to use to write data from a source .txt to an SQL table

Import-Csv $_.fullname -Header Z_AP_EXNUM,Z_AP_ID | ForEach-Object {Invoke-Sqlcmd `
        -Database $database -ServerInstance $server -Username $uid -Password $pwd `
        -Query "insert into $table VALUES ('$_.Z_AP_EXNUM','$_.Z_AP_ID')"

My actual code has about 293 headers/columns to insert. The error I receiving is that String or binary data will be truncated. The statement has been terminated. I went into one of my test files and removed all the data but a few standard fields and still receive these errors. I am not sure what could be causing the insert to fail.

Error:

Invoke-Sqlcmd : String or binary data would be truncated. The statement has been terminated. At C:\Users***\Desktop\script\scripy.ps1:79 char:111 + Z_AP_STR_CSEM5_9,Z_AP_STR_NAME5_10,Z_AP_STR_SCOR5_10,Z_AP_STR_CSEM5_10 | ForEach-Object {Invoke-Sqlcmd <<<< ` + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Solution

  • Check the table schema, this error usually shows due to the difference of column definition type in the table and the powershell data type you want to store in it.