SqlBulkCopy problem

Question

SqlBulkCopy problem

asp.net c# sqlbulkcopy sql-server-2005

Question

i use SqlBulkCopy to insert data from OleDbDataReader (contains data from xls) to mssql-2005 i have a cloumn on the OleDbDataReader that contains number stored as text (in the xls)

when i look into the mssql data i see null in that column all other columns are move ok.

link text

eyalb

Popular Answer

When one bulk copies from SQL server to SQL Server, the source is able to indicate very well the data types to the destination. With Excel, the source makes guesses about the data type. You may have the Excel formatted as text, but the source (ODBC?) might look at the first 50 rows and guess that it's a numeric datatype. Even if the column names match, if the data types are different I suspect the column doesn't get populated.

HolyOne

View more on Stack Overflow

Accepted Answer

you need to map columns like in the code...

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
        {
            bulkCopy.ColumnMappings.Add("ID", "ID");
            bulkCopy.ColumnMappings.Add("Email", "Email");
            bulkCopy.DestinationTableName = "tableName";
            bulkCopy.WriteToServer(ExcelReader);
        }

SqlBulkCopy problem

Question

Accepted Answer

Popular Answer

Performance

Expression Evaluator