PowerShell pipe / SQL insert query data limits (and increasing them?)

Question

Are there any limits to the size of a string you can assign to a variable in powershell or any limits to the size of the text sent within an SQL INSERT query?

I have a big CSV file coming in to PowerShell and through string construction in a foreach loop I am generating SQL INSERT queries for each row. The resulting INSERT query; INSERT query; is over about 4MB.

The SQL server has a perfect schema to receive the data, however, when sending the 4MB collection of INSERT queries (each seperated by ;) I get an error that looks to me like the long 4MB set of insert queries was truncated somehow. I guess I have hit some kind of limit.

Is there a way of getting around this (programatically in PowerShell) or a way of increasing the size limit of an acceptable collection of SQL INSERT queries?

My code is using System.Data.SqlClient.SqlConnection and System.Data.sqlclient.SqlCommand.

Smaller datasets work ok but the larger datasets give an error like the following example. Each different dataset gives off a different "Incorrect syntax near" indicator.

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax
near '('."
At C:\Users\stuart\Desktop\git\ADStfL\WorkInProgress.ps1:211 char:3
+         $SQLCommand.executenonquery()
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Answer 1

In my experience, the best performing way to do this is to load the CSV into a DataTable and then use SQLBulkCopy.

$ErrorActionPreference = 'Stop';

$Csv = Import-Csv -Path $FileName;

$SqlServer = 'MyServer';
$SqlDatabase = 'MyDatabase';
$DestinationTableName = 'MyTable';

# Create Connection String
$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;


# Define your DataTable.  The column order of the DataTable must either match the table in the database, or 
# you must specify the column mapping in SqlBulkCopy.ColumnMapping.  If you have an IDENTITY column, it's a
# bit more complicated
$DataTable = New-Object -TypeName System.Data.DataTable -ArgumentList $DestinationTableName;

$NewColumn = $DataTable.Columns.Add('Id',[System.Int32]);
$NewColumn.AllowDBNull = $false;

$NewColumn = $DataTable.Columns.Add('IntegerField',[System.Int32]);
$NewColumn.AllowDBNull = $false;

$NewColumn = $DataTable.Columns.Add('DecimalField',[System.Decimal]);
$NewColumn.AllowDBNull = $false;

$NewColumn = $DataTable.Columns.Add('VarCharField',[System.String]);
$NewColumn.MaxLength = 50;

$NewColumn = $DataTable.Columns.Add('DateTimeField',[System.DateTime]);
$NewColumn.AllowDBNull = $false;


# Populate your datatable from the CSV file
# You may find that you need to type cast some of the fields.
$Csv | ForEach-Object {
    $NewRow = $DataTable.NewRow();
    $NewRow['Id'] = $_.Id;
    $NewRow['IntegerField'] = $_.IntegerField;
    $NewRow['DecimalField'] = $_.DecimalFiled;
    $NewRow['StringField'] = $_.StringField1;
    $NewRow['DateTimeField'] = $_.DateTimeField1;

    $DataTable.Rows.Add($NewRow);
}

# Create Connection
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;

# Open Connection
$SqlConnection.Open();

# Start Transaction
$SqlTransaction = $SqlConnection.BeginTransaction();

# Double check the possible options at https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=vs.110).aspx
# If you need multiple then -bor them together
$SqlBulkCopyOptions = [System.Data.SqlClient.SqlBulkCopyOptions]::CheckConstraints;

# Create SqlBulkCopy class
$SqlBulkCopy = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy -ArgumentList $SqlConnection, $SqlBulkCopyOptions, $SqlTransaction;

# Specify destination table
$SqlBulkCopy.DestinationTableName = $DestinationTableName;

# Do the insert; rollback on error
try {
    $SqlBulkCopy.WriteToServer($DataTable);
    $SqlTransaction.Commit();
}
catch {
    # Roll back transaction and rethrow error
    $SqlTransaction.Rollback();
    throw ($_);
}
finally {
    $SqlConnection.Close();
    $SqlConnection.Dispose();
}

The other method is to use an SQLCommand and do it row by row:

$ErrorActionPreference = 'Stop';

$Csv = Import-Csv -Path $FileName;

$SqlServer = 'MyServer';
$SqlDatabase = 'MyDatabase';

# Create Connection String
$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;

# Create Connection
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;

# Create Command
$InsertCommandText = 'INSERT INTO DestinationTable (Id, IntegerField, DecimalField, StringField, DateTimeField) VALUES (@Id, @IntegerField, @DecimalField, @StringField, @DateTimeField)';
$InsertCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $SqlConnection;

[void]$InsertCommand.Parameters.Add('@Id', [System.Data.SqlDbType]::Int);
[void]$InsertCommand.Parameters.Add('@IntegerField', [System.Data.SqlDbType]::Int);
[void]$InsertCommand.Parameters.Add('@DecimalField', [System.Data.SqlDbType]::Decimal);
[void]$InsertCommand.Parameters.Add('@StringField', [System.Data.SqlDbType]::VarChar,50);
[void]$InsertCommand.Parameters.Add('@DateTimeField', [System.Data.SqlDbType]::DateTime);

# Open connection and start transaction
$SqlConnection.Open()
$SqlTransaction = $SqlConnection.BeginTransaction();
$InsertCommand.Transaction = $SqlTransaction;
$RowsInserted = 0;  

try {
    $line = 0;
    $Csv | ForEach-Object {
        $line++;

        # Specify parameter values
        $InsertCommand.Parameters['@Id'].Value = $_.Id;
        $InsertCommand.Parameters['@IntegerField'].Value  = $_.IntegerField;
        $InsertCommand.Parameters['@DecimalField'].Value = $_.DecimalField;
        $InsertCommand.Parameters['@StringField'].Value = $_.StringField;
        $InsertCommand.Parameters['@DateTimeField'].Value = $_.DateTimeField;

        $RowsInserted += $InsertCommand.ExecuteNonQuery();

        # Clear parameter values
        $InsertCommand.Parameters | ForEach-Object { $_.Value = $null };
    }
    $SqlTransaction.Commit();
    Write-Output "Rows affected: $RowsInserted";
}
catch {
    # Roll back transaction and rethrow error
    $SqlTransaction.Rollback();
    Write-Error "Error on line $line" -ErrorAction Continue;
    throw ($_);
}
finally {
    $SqlConnection.Close();
    $SqlConnection.Dispose();
}

Edit: Oh, I forgot one important point. If you need to set the value of a field to null in the database, you need to set it's value to [System.DBNull]::Value, not $null.