SQL Bulk DestinationTableName

c# database sqlbulkcopy sql-server

Question

I use a.csv a file to a database on my SQL Server in bulk. The issue isDestinationTableName because when I utilize a string forDestinationTableName I get an error exception

System.InvalidOperationException: Cannot access destination table

as the screenshot demonstrates.

If "test" is used, such as copy.DestinationTableName = "test," everything is great.

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);
    }
}
1
1
4/5/2018 4:22:29 AM

Accepted Answer

similar to what you did when you made the table

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

To get around the hyphen in the table name, the solution is presumably to escape it, like

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

Note: If at all feasible, stay with names that don't need escaping. However, if it works.

2
4/4/2018 11:16:12 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow