SqlBulkCopy error

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

1
0
8/16/2011 4:27:33 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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);
        }
3
12/1/2009 11:49:58 AM

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

Related Questions

How can I resolve this stored procedure issue?

Just as an alternative solution you could use ...database triggers... to update the second table.
sql sqlbulkcopy sql-server
asked by mat-mcloughlin

Identity issue while uploading a csv file to a SQL server

Use SqlBulkCopy.ColumnMappings...http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmapping.aspx
.net c# csv sqlbulkcopy sql-server
asked by Mikael Eriksson

Bulk SQL Copy YYYYMMDD issue

Older issue, but wanted to add an alternative approach....I had the same issue with SQLBulkLoader not allowing DataType/culture specifications for columns when streaming from IDataReader....In order to reduce the speed overhead of constructing datarows locally and instead have the parsing occur on the target, a simple method I used was to temporari...
c# date sqlbulkcopy
asked by Rob Knights

What is the difference between passing SqlBulkCopyOptions and passing SqlBulkCopy? InternalTransaction is used but not passed?

I am trying to find the difference between using SqlBulkCopy with the ...SqlBulkCopyOptions.UseInternalTransaction... copy option and without it, but in my test application I do not detect any difference. If ...BatchSize... is for example 0 and I add 100 records (in a ...DataTable...) where record number 50 causes an error when adding it to the dat...
.net sqlbulkcopy sql-server windows
asked by Jürgen Bayer

Debugging SqlBulkCopy problems when it doesn't return any errors

Right now my problem with sqlBulkCopy is that it finishes without any errors but when i look into the table i can't see any data....The try catch block catches no errors and am i missing something? Because i don't see any problems with my code...This is the code i use:...DataTable table = new DataTable(); table.Columns.Add(new DataColumn("Buying ID...
c# sqlbulkcopy
asked by Gabriel

SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value

“SQLBulkCopy column does not allow DbNull.value” error is due to source and destination table has different column order.
c# default-constraint null sqlbulkcopy sql-server
asked by Anasuddeen

SqlBulkCopy hangs without any exception or timeout when inserting a datatable with even a single row

Having an issue, basically I have a very large datatable (50,000 rows) and I am using SqlBulkCopy to insert it via this function I wrote:...public static void DoSqlBulkCopy(string connectionString, string destinationTableName, DataTable dataTable, int batchSize = 2000) { using (var bulk = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Che...
c# sqlbulkcopy
asked by NibblyPig

Why do I get high fragmentation when using SqlBulkCopy to move large amounts data between databases?

Using the code ...Using bcp As New SqlBulkCopy(destConnection) bcp.DestinationTableName = "myOutputTable" bcp.BatchSize = 10000 bcp.WriteToServer(reader) End Using ...Where reader is an essentially an IDataReader that reads in a table, containing 200k rows or so....The input table looks like this...CREATE TABLE [dbo].[MyTable]( ...
indexing sql sqlbulkcopy sql-server
asked by Paul

Problems importing into database from CSV and using SqlBulkCopy

I have a windows form application in which a user chooses a .csv file and clicks import. At this point the file should import into a SQL Server using SqlBulkCopy. Problem is I am getting some errors and I think its due to fields being empty on the import. Im getting this error:...Input string was not in a correct format.Couldn't store <> in ScranDr...
csv sql sqlbulkcopy vb.net
asked by Brownd92

Problem with Entity Framework extensions .BulkSaveChanges - Saving Guid

I'm using EF extension to do ...BulkSaveChanges..., and I'm using SQLite, everything works perfectly, fast saving, etc. The problem I'm having is that when saving Guids, it's as if ...BulkSaveChanges... is converting the Guids to TEXT first before saving, but the data type is BLOB, then when retrieved from the database through DbContext, GUIDs are ...
.net-core entity-framework-core sqlbulkcopy sqlite
asked by Mac

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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