Porblem with SQLBulkCopy when Column in Primary Key with UniqueIdentifier

Question

I am trying to insert data into a table with a primary key having default value of newsequentialid()  using SqlBulkCopy.

Prior to insert the data by examining DataReader object I see that all PK values are Guid.Empty(represented by 0000-000…) . However when I try insert the data the SQlBulkCopy  tries to  insert PK with Guid.Empty values and I get unique identifier conflicts for PK column.

Is there a way to exclude Pk from insert or tell SQLBulkCopy not insert data in column with UniqueIdentifier property?   I have tried all SqlBulkCopyOptions Enumerations with no success.

Answer 1

You'll need to use a SqlBulkCopyColumnMappingCollection (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmappingcollection.aspx).  Just omit the uniqueidentifier from the mapping.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Thanks Dan ColumnMapping did the trick.

Answer 3

Hello Gene Ariani,

It seems that the Dan's Guzman post has helped you to solve your problem. If it is right, please , could you mark the Dan'post as Answer ? ( it is easy : you have only to click on the "Mark As Answer" part at the bottom of the Dan's post and some seconds later, the thread will be redisplayed with the indication that this thread is answered ). It is important that the original poster does it by himself as it is the best and the simplest way to tell : thanks. Moreover, an answered thread is always considered as more attractive and reliable than an unanswered one.

Thanks beforehand.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.