Excel to SQL. Alternative to BulkCopy?

I have a piece of code which copies data from an excel spreadsheet to a MSSQL table using DataReader and SqlBulkCopy. It worked fine until I created a primary key on the table and now it fails. I am first deleting the contents of the SQL table before filling it again with the data from excel.

As it is only a small amount of data I am moving, I wondered if there was a better way to do this than using BulkCopy?

Update: below is the relative code and the error I receive is: "The given value of type String from the data source cannot be converted to type float of the specified target column."

using (OleDbConnection connection = new OleDbConnection(excelConnectionStrin开发者_如何学Pythong))
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand
                ("SELECT Name, Date, Amount FROM ExcelNamedRange", connection);

                using (OleDbDataReader dr = cmd.ExecuteReader())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "SqlTable";
                        bulkCopy.WriteToServer(dr);

                    }
                }                 
            }

SqlBulkCopy automatically maps the fields. But since you added a primary key that default mapping is no longer valid.

You will have to set ColumnMapping to tell your SqlBulkCopy object explicityly how to map the fields.

Do this for all your fields, except the primary key (assuming you use an identity on the PK).

For example:

_bulkCopyEngine.ColumnMappings.Add("fieldname_from", "fieldname_to");

Creating a primary key, suggests you are enforcing a domain constraint (a good thing).

Therefore, your actual problem is not that you need another way to perform the bulk insert, but that you need to find out why you have duplicate keys (the precise reason for enforcing the PK).

BulkCopy should work just fine, so your problem seems to be a duplicate key (what's the error message?). You either have data that that is wrong there, or the primary key you created is too narrow.

What you could also do is push the data into a staging table first (no keys/ indexes etc, just a plain table) and then use an update (merge when on 2008) statement to put it into the actual table.

GJ

ok that seems to be a different problem altogether, there seems to be a value in year ExcelNamedRange that cannot be cast as one of the columns in SqlTable. Can you see any? Maybe division by 0 error etc?

Also make sure the columns line up. Not sure exactly how SqlBulkCopy maps the columns, I think it just puts the first column from NamedRange into the first column of SqlTale etc. So make sure they;re in the right order. (or see what happens if you change the names)

The BulkCopy is fastest way, how to insert data into MSSQL from C#.