CODEWITHSUNDEEP
c#.netsql-serversqlbulkcopy
Deltax76
Deltax76

Reputation: 14263

How to automatically truncate string when do bulk insert?

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.

Upvotes: 12

Views: 10812

Answers (3)

Rahul
Rahul

Reputation: 166

Try Using SQLTransaction Class while using SQLBulkCopy Class

Upvotes: -1

gbn
gbn

Reputation: 432271

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

Upvotes: 8

Shekhar_Pro
Shekhar_Pro

Reputation: 18430

Unfortunately there is no direct way of doing that with SqlBulkCopy. SQL Bulk Inserts are by nature almost "dumb" but that's why they are so fast. They aren't even logged (except capturing SqlRowsCopied event) so if something fails, there's not much information. What you're looking for would in a way, be counter to the purpose of this class

But there can be 2 possible ways:

  • You can try using SqlBulkCopyOptionsEnumeration (passed to SqlBulkCopy() Constructor) and use SqlBulkCopyOptions.CheckConstraints (Check constraints while data is being inserted. By default, constraints are not checked.).

  • Or you can use SqlBulkCopyOptions.FireTriggers Enumeration (When specified, cause the server to fire the insert triggers for the rows being inserted into the database.) and handle the exception in SQL Server Insert Trigger.

Upvotes: 6

Related Questions