Timeout expired with SqlBulkCopy
You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.
sqlbulkcopy
Any way to SQLBulkCopy “insert or update if exists”?
I published a nuget package (SqlBulkTools) to solve this problem. Here’s a code example that would achieve a bulk upsert. var bulk = new BulkOperations(); var books = GetBooks(); using (TransactionScope trans = new TransactionScope()) { using (SqlConnection conn = new SqlConnection(ConfigurationManager .ConnectionStrings[“SqlBulkToolsTest”].ConnectionString)) { bulk.Setup<Book>() .ForCollection(books) .WithTable(“Books”) .AddAllColumns() .BulkInsertOrUpdate() .MatchTargetOn(x => x.ISBN) .Commit(conn); } trans.Complete(); … Read more
The given ColumnMapping does not match up with any column in the source or destination
It’s important to keep in mind sqlBulkCopy columns are case sensitive for some versions of SQL. I think MSSQL 2005. Hope it helps
Mapping columns in a DataTable to a SQL table with SqlBulkCopy
You probably need some thing like public void BatchBulkCopy(DataTable dataTable, string DestinationTbl, int batchSize) { // Get the DataTable DataTable dtInsertRows = dataTable; using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = DestinationTbl; // Number of records to be processed in one go sbc.BatchSize = batchSize; // Add your column mappings here sbc.ColumnMappings.Add(“field1″,”field3”); sbc.ColumnMappings.Add(“foo”,”bar”); … Read more
Is it possible to use System.Transactions.TransactionScope with SqlBulkCopy?
SqlBulkCopy never enlists into a transaction. SqlCommand also does not do that. Common misconception. The enlistment is performed at the time SqlConnection.Open is called. After that, anything that runs on that connection is part of the transaction implicitly. In fact it is no longer allowed to pass an explicit transaction. If you want SqlBulkCopy to … Read more
Get an IDataReader from a typed List
You can use Marc Gravell’s FastMember: IDataReader reader = ObjectReader.Create(myEnumerable); //all columns IDataReader reader = ObjectReader.Create(myEnumerable, “Id”, “Name”, “Description”); In addition to abstracting away the creation logic, it also uses IL to get the property/field values, which is faster than reflection.
SqlBulkCopy – The given value of type String from the data source cannot be converted to type money of the specified target column
For the people stumbling across this question and getting a similar error message in regards to an nvarchar instead of money: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. This could be caused by a too-short column. For example, if your column … Read more
Bulk inserts taking longer than expected using Dapper
The best I was able to achieve was 50k records in 4 seconds using this approach SqlTransaction trans = connection.BeginTransaction(); connection.Execute(@” insert Member(Username, IsActive) values(@Username, @IsActive)”, members, transaction: trans); trans.Commit();
SqlBulkCopy Insert with Identity Column
To have the destination table assign the identity, DO NOT use the SqlBulkCopyOptions.KeepIdentity option. Instead, don’t map the identity from the source, and don’t extract it from source to send through to SqlBulkCopy.