Before using SqlBulkCopy, truncate.

When doing bulk inserts, how can you automatically truncate strings?

Always use a staging/load table for bulk actions. ...Then you can process, clean, scrub etc the data before flushing to the real table. This includes, LEFTs, lookups, de-duplications etc...So:...Load a staging table with ...wide... columns...Flush from staging to "real" table using ...INSERT realtable (..) SELECT LEFT(..), .. FROM Staging

When SQLBulkCopy is used with Transaction, an event is fired each time a batch is copied.

I have finally decided to use an internal transaction. ATM it seems to be working fine if an error is encountered the current batch is rolled back. Regarding Ben's comment above, i tried this but its not possible, its not as though it loops through a section of code where you could raise an event. In the end i used the notifyafter property, which r...

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

If you set this option then the SQLBulkCopy class will add a..._internalTransaction = _connection.BeginTransaction(); ...around each batch. ...But this option makes no practical difference with SQL Server as transactions by default run in auto commit mode anyway....The only observable difference is that it performs validation that you haven't also ...

sqlbulkcopy from Excel via ACE.OLEDB truncates text to 255 chars

I used ODBC instead of the OLEDB and it doesn't truncate the values to 255 symbols anymore:...OdbcConnection con = new OdbcConnection(@"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\testbook.xls"); OdbcCommand cmd = new OdbcCommand(@"SELECT * FROM [Workbook1$]"); cmd.Connection = con; OdbcDataAdapter da = new OdbcDataAdapter(cmd); DataSet ds ...

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

Update: ...Here is my solution...I have a table defined as:...CREATE TABLE [dbo].[csvrf_References] ( [Ident] [int] IDENTITY(1,1) NOT NULL, [ReferenceID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()), [Type] [nvarchar](255) NOT NULL, [Location] [nvarchar](1000) NULL, [Description] [nvarchar](2000) NULL, [CreatedOn]...

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

Fixed it. Spelt the table name wrong, was inserting into CuteCats instead of CuteCat. It hung with no error message. Blasted thing!

string filed is truncated on SqlBulkCopy insert

I think the issues is with the OLEDB provider for excel....When you use OLEDB providers then the datatype is determined automatically by the provider based on the first 8 rows. If you have lengthy cells in the first 8 rows then data type will be set as text and otherwise it will be memo type which can hold 255 characters. To overcome this issue Eit...

SqlBulkCopy - The given ColumnName does not match up with any column in the source or destination

According to MSDN (...here...), the ...DataColumn.ToString()... method returns "...The Expression value, if the property is set; otherwise, the ColumnName property....". ...I've always found the ...ToString()... method to be wonky anyway (can change based on current state/conditions), so I'd recommend using the ...ColumnName... property instead, a...

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

Its hard to tell without more data but I bet that your timestamp being a clustered index is causing this. Try sorting the data by this field before sending to the output table.

My Sql Bulk inserts loading the data with truncating

As per my understanding, below are few points that you want to achieve:... 1) Data should not get truncated if title length is more than specified field length as per table structure.... 2) If title length is more, then that record should get skipped while doing an importing of records & rest of the process should continue ahead....Answer as per my...