same columns for different tables using sqlbulkcopy

Hi in my web application, I have 4 Data base tables and 3 tables are have same column names and how can i take in sqlbulkcopy method. Can anyone please tell me

objbulk.DestinationTableName = "Adjustments";

objbulk.DestinationTableName = "Payments";

objbulk.DestinationTableName = "Charges";
objbulk.ColumnMappings.Add("ID", "ID");
objbulk.ColumnMappings.Add("Description", "Description");
objbulk.ColumnMappings.Add("Credits", "Credits");
objbulk.ColumnMappings.Add("QTY%", "QTYInPercentage");
objbulk.ColumnMappings.Add("Amount", "Amount");
objbulk.ColumnMappings.Add("Amount %", "AmountInPercentage");
objbulk.ColumnMappings.Add("Client", "Client");
con.Open();
objbulk.WriteToServer(dt);
con.Close();

Thank you

SqlBulkCopy class efficiently bulk load a SQL Server table with data from another source.Using WriteToServer() you can insert multiple records in a single shot. As per your code, if you assigning Destination again then it takes latest table name.

Try like below code to insert in multiple tables.

DataTable dt = new DataTable(); // Get data from other sources
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName = "dbo.Adjustments";
bulkCopy.ColumnMappings.Add("ID", "ID");
bulkCopy.ColumnMappings.Add("Description", "Description");
bulkCopy.ColumnMappings.Add("Credits", "Credits");
// Write readerHeader to the destination.
try
{
bulkCopy.WriteToServer(dt.Tables[0]);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerHeader.Close();
}
// Set up the order Payments destination.
bulkCopy.DestinationTableName ="dbo.Payments";
// Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear();
// Add order detail column mappings.
bulkCopy.ColumnMappings.Add("Credits", "Credits");
bulkCopy.ColumnMappings.Add("QTY%", "QTYInPercentage");
bulkCopy.ColumnMappings.Add("Amount", "Amount");
bulkCopy.ColumnMappings.Add("Amount %", "AmountInPercentage");
bulkCopy.ColumnMappings.Add("Client", "Client");
// Write readerDetail to the destination.
try
{
bulkCopy.WriteToServer(dt.Tables[1]);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerDetail.Close();
}
}

https://msdn.microsoft.com/en-us/library/s4s223c6(v=vs.110).aspx

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
SqlBulkCopy class efficiently bulk load a SQL Server table with data from another source.Using WriteToServer() you can insert multiple records in a single shot. As per your code, if you assigning Destination again then it takes latest table name.

Try like below code to insert in multiple tables.
DataTable dt = new DataTable(); // Get data from other sources
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
    bulkCopy.DestinationTableName = "dbo.Adjustments";

    bulkCopy.ColumnMappings.Add("ID", "ID");
    bulkCopy.ColumnMappings.Add("Description", "Description");
    bulkCopy.ColumnMappings.Add("Credits", "Credits");

    // Write readerHeader to the destination.
    try
    {
        bulkCopy.WriteToServer(dt.Tables[0]);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        readerHeader.Close();
    }

    // Set up the order Payments destination.
    bulkCopy.DestinationTableName ="dbo.Payments";

    // Clear the ColumnMappingCollection.
    bulkCopy.ColumnMappings.Clear();

    // Add order detail column mappings.
    bulkCopy.ColumnMappings.Add("Credits", "Credits");
    bulkCopy.ColumnMappings.Add("QTY%", "QTYInPercentage");
    bulkCopy.ColumnMappings.Add("Amount", "Amount");
    bulkCopy.ColumnMappings.Add("Amount %", "AmountInPercentage");
    bulkCopy.ColumnMappings.Add("Client", "Client");

    // Write readerDetail to the destination.
    try
    {
        bulkCopy.WriteToServer(dt.Tables[1]);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        readerDetail.Close();
    }
}
https://msdn.microsoft.com/en-us/library/s4s223c6(v=vs.110).aspx