[Solved] SQLBulkCopy Error: Exception of type System.OutOfMemoryException was thrown

Hiamar,

Use below code that will split the record into multiple table and looping through the list inserting to table using sqlbulkcopy.

C#

protected void Page_Load(object sender, EventArgs e)
{
    using (SqlConnection sourceConnection = new SqlConnection(connectionString))
    {
        sourceConnection.Open();
        SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", sourceConnection);
        long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
        Console.WriteLine("Starting row count = {0}", countStart);
        SqlCommand commandSourceData = new SqlCommand("SELECT ProductID, Name,ProductNumber FROM Production.Product;", sourceConnection);
        SqlDataAdapter sda = new SqlDataAdapter(commandRowCount);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        List<DataTable> dts = SplitDataTableToMultiple(dt, 100);
        foreach (DataTable dataTable in dts)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
            {
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";
                // Set the BatchSize.
                bulkCopy.BulkCopyTimeout = 0;
                try
                {
                    bulkCopy.WriteToServer(dataTable);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }
}

private static List<DataTable> SplitDataTableToMultiple(DataTable originalTable, int batchSize)
{
    List<DataTable> dts = new List<DataTable>();
    DataTable dt = new DataTable();
    dt = originalTable.Clone();
    int j = 0;
    int k = 1;
    if (originalTable.Rows.Count <= batchSize)
    {
        dt.TableName = "Table_" + k;
        dt = originalTable.Copy();
        dts.Add(dt.Copy());
    }
    else
    {
        for (int i = 0; i < originalTable.Rows.Count; i++)
        {
            dt.NewRow();
            dt.ImportRow(originalTable.Rows[i]);
            if ((i + 1) == originalTable.Rows.Count)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
            }
            else if (++j == batchSize)
            {
                dt.TableName = "Table_" + k;
                dts.Add(dt.Copy());
                dt.Rows.Clear();
                k++;
                j = 0;
            }
        }
    }

    return dts;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Using sourceConnection As SqlConnection = New SqlConnection(connectionString)
        sourceConnection.Open()
        Dim commandRowCount As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", sourceConnection)
        Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
        Console.WriteLine("Starting row count = {0}", countStart)
        Dim commandSourceData As SqlCommand = New SqlCommand("SELECT ProductID, Name,ProductNumber FROM Production.Product;", sourceConnection)
        Dim sda As SqlDataAdapter = New SqlDataAdapter(commandRowCount)
        Dim dt As DataTable = New DataTable()
        sda.Fill(dt)
        Dim dts As List(Of DataTable) = SplitDataTableToMultiple(dt, 100)
        For Each dataTable As DataTable In dts
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
                bulkCopy.BulkCopyTimeout = 0

                Try
                    bulkCopy.WriteToServer(dataTable)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
            End Using
        Next
    End Using
End Sub

Private Shared Function SplitDataTableToMultiple(originalTable As DataTable, batchSize As Integer) As List(Of DataTable)
    Dim dts As List(Of DataTable) = New List(Of DataTable)()
    Dim dt As DataTable = New DataTable()
    dt = originalTable.Clone()
    Dim j As Integer = 0
    Dim k As Integer = 1
    If originalTable.Rows.Count <= batchSize Then
        dt.TableName = "Table_" & k
        dt = originalTable.Copy()
        dts.Add(dt.Copy())
    Else
        For i As Integer = 0 To originalTable.Rows.Count - 1
            dt.NewRow()
            dt.ImportRow(originalTable.Rows(i))
            If (i + 1) = originalTable.Rows.Count Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
            ElseIf Threading.Interlocked.Increment(j) = batchSize Then
                dt.TableName = "Table_" & k
                dts.Add(dt.Copy())
                dt.Rows.Clear()
                k += 1
                j = 0
            End If
        Next
    End If

    Return dts
End Function