How to automatically truncate string when do bulk insert?

c# .net sql-server sqlbulkcopy
Quan Mai picture Quan Mai · Jun 19, 2011 · Viewed 8.4k times · Source

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.

Answer

gbn picture gbn · Jun 19, 2011

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

Related questions

SqlBulkCopy and DataTables with Parent/Child Relation on Identity Column

We have a need to update several tables that have parent/child relationships based on an Identity primary-key in the parent table, which is referred to by one or more child tables as a foreign key. Due to the high …

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
What is the equivalent of bigint in C#?

What am I supposed to use when handling a value in C#, which is bigint for an SQL Server database?

Read More