CODEWITHSUNDEEP
c#asp.netsqlbulkcopy
L. Achilles
L. Achilles

Reputation: 133

Cannot access destination table on bulk insert c#

I am trying to use SqlBulkCopy in order to insert plenty of rows in short time. I get the error:"

Cannot access destination table myTable.

Here is my code:

string conn = ConfigurationManager.ConnectionString["myConnection"].ToString();
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Col1", typeof(string)));
dt.Columns.Add(new DataColumn("Col2", typeof(int)));
for (int i=0;i<10000;i++)
{
   DataRow dr = dt.NewRow();
   dr["Col1"] = "Col1_" + i.toString();
   dr["Col2"] = i;
}
using (SqlBulkCopy bulkSql = new SqlBulkCopy(conn))
{
   bulkSql.BatchSize = 10000;
   bulkSql.BulkCopyTimeout = 10000;
   bulkSql.ColumnMappings.Clear();
   bulkSql.ColumnMappings.Add("Col1", "Col1");
   bulkSql.ColumnMappings.Add("Col2", "Col2");
   bulkSql.DestinationTableName = "[myTable]"
   bulkSql.WriteToServer(dt);
}

Upvotes: 3

Views: 3932

Answers (1)

L. Achilles
L. Achilles

Reputation: 133

I finally have found the answer. My table name is in [MyDatabase].[mySchema].[myTable]. So in web.config my connection string correctly had Initial Catalog=MyDatabase; but I also needed: bulkSql.DestinationTableName = "[mySchema].[myTable]"; in order to run it succesfully. Thank you all for your help!

Upvotes: 3

Related Questions