SQL Bulk DestinationTableName

c# database sqlbulkcopy sql-server

Question

I use a .csv file to bulk insert into my SQL Server database. The problem is DestinationTableName because when I use a string for DestinationTableName, I get error exception

System.InvalidOperationException: Cannot access destination table

as you can see in the screenshot.

If a use "test" like copy.DestinationTableName = "test"; it works fine

enter image description here

string symbolName = dt.Rows[1][0].ToString();

string strConnection = @"Data Source =.\SQLEXPRESS; AttachDbFilename = C:\USERS\JEF\DOCUMENTS\DATABASE1.MDF; Integrated Security = True; Connect Timeout = 30; User Instance = True";
SqlConnection condb2 = new SqlConnection(strConnection);

string createTablerow ="create table ['"+symbolName+"'] (code1 VARCHAR(100) COLLATE Arabic_CI_AI_KS_WS,date1 varchar(50),open1 varchar(50),high1 varchar(50),low1 varchar(50),close1 varchar(50),vol1 varchar(50))";

using (SqlConnection connection = new SqlConnection(strConnection))
{
    SqlCommand command1 = new SqlCommand(createTablerow, connection);
    connection.Open();
    command1.ExecuteNonQuery();
}

using (SqlConnection cn = new SqlConnection(strConnection))
{
    cn.Open();

    using (SqlBulkCopy copy = new SqlBulkCopy(cn))
    {
        copy.ColumnMappings.Add(0, "code1");
        copy.ColumnMappings.Add(1, "date1");
        copy.ColumnMappings.Add(2, "open1");
        copy.ColumnMappings.Add(3, "high1");
        copy.ColumnMappings.Add(4, "low1");
        copy.ColumnMappings.Add(5, "close1");
        copy.ColumnMappings.Add(6, "vol1");

        copy.DestinationTableName = symbolName;

        copy.WriteToServer(dt);
    }
}

Accepted Answer

Just like you did when you created the table:

"create table ['"+symbolName+"']

the trick here is probably to escape the table name to compensate for the hyphen in it, so:

copy.DestinationTableName = "[" + symbolName + "]";

Note: if possible, it is usually preferable to stick to names that don't need escaping. But... if it works.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why