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

.net c# sqlbulkcopy sql-server

Question

I want to insert many rows (constructed from Entity Framework objects) to SQL Server. The problem is, some of string properties have length exceeded length of column in database, which causes an exception, and then all of rows will unable to insert into database.

So I wonder that if there is a way to tell SqlBulkCopy to automatically truncate any over-length rows? Of course, I can check and substring each property if it exceeds the limited length, before insert it in to a DataTable, but it would slow down the whole program.

1
12
6/19/2011 5:02:21 AM

Popular Answer

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:

  1. Load a staging table with wide columns
  2. Flush from staging to "real" table using INSERT realtable (..) SELECT LEFT(..), .. FROM Staging
9
6/19/2011 9:14:39 AM


Related Questions





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