如何自动截断字符串时，做批量插入？ [英] How to automatically truncate string when do bulk insert?

问题描述

我想插入许多行（从实体框架对象构造）到SQL Server。问题在于，一些串属性的具有长度超过列的数据库长度，这导致异常，然后所有行的将无法插入到数据库中。

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.

所以我想，如果有一种方法来告诉SqlBulkCopy的自动截断过任何长度行？当然，我可以检查并串每个属性如果超过限定长度，前插入一个数据表，但它会拖慢整个程序。

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.

推荐答案

始终使用批量操作登台/负载表。

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

所以：

1. 加载一个临时表的宽的列
2. 从使用 INSERT realtable升级至真正的表的同花顺（..）选择LEFT（..）..从登台

1. Load a staging table with wide columns
2. Flush from staging to "real" table using INSERT realtable (..) SELECT LEFT(..), .. FROM Staging

这篇关于如何自动截断字符串时，做批量插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！