After some goоgling, I could not find a proper replacement of SQLBulkCopy from SQLClient in TeraData. Can any body suggest me anything like SQLBulkCopy for TeraData to be used in c#? I need to insert up-to a few millions of rows in TD Need this to compare a set a rows retrieved from external DB and dumped into TD and compare with data already available in TeraData.
Any suggestion is appreciated.
I couldn't find an equivalent but this is acceptably fast for my purposes. Also I suspect that the UpdateBatchSize could be tweaked to match your particular data to increase speeds.
As written, your source and destination tables must have the same columns (like BulkCopy, although not necessarily in the same order).
TdConnection tdCon = new TdConnection(tdConString);
SqlConnection sqlCon1 = new SqlConnection(serverOneConString);
// Get schema for destination table
var query = "SELECT * FROM [Destination_Table] where 0 = 1";
using (TdDataAdapter insertAdapter = new TdDataAdapter(query, tdCon))
{
DataSet ds = new DataSet();
insertAdapter.Fill(ds);
// Load data from source table
using (SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM [Source_Table]", sqlCon1)) {
dataAdapter.SelectCommand.CommandTimeout = 240;
dataAdapter.Fill(dt);
}
// Move data from source to destination, matching column names
foreach (DataRow row in dt.Rows) {
var newRow = ds.Tables[0].NewRow();
foreach (DataColumn column in dt.Columns) {
newRow[column.ColumnName] = row[column.ColumnName];
}
ds.Tables[0].Rows.Add(newRow);
}
TdCommandBuilder builder = new TdCommandBuilder(insertAdapter);
insertAdapter.UpdateBatchSize = 250;
insertAdapter.Update(ds);
}