How can use SQLBulkCopy on a table with a GUID primary key and default newsequentialid() ?

Question

When using SQLBulkCopy on a table with a GUID primary key and default newsequentialid()

e.g

CREATE TABLE [dbo].[MyTable](
[MyPrimaryKey] [uniqueidentifier] NOT NULL CONSTRAINT [MyConstraint]  DEFAULT (newsequentialid()),
[Status] [int] NULL,
[Priority] [int] NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
(
[MyPrimaryKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

wIth the C# code

        tran = connection.BeginTransaction();
        SqlBulkCopy sqlCopy = new SqlBulkCopy(connection,SqlBulkCopyOptions.Default, tran);            

        sqlCopy.DestinationTableName = "MyTable";            
        sqlCopy.WriteToServer(dataTable);

Gives you an error...

Column 'MyPrimaryKey' does not allow DBNull.Value

I've tried fiddling the the SqlBulkCopyOptions. The only thing that works is setting the MyPrimaryKey field to allow nulls and removing the primary key.

Anyone know if there is a workaround for this issue? Or can you verify that there is no workaround (other than changing the table structure)?

Answer 1

You're only options are to remove the MyPrimaryKey field from the data that is being loaded or to modify the table structure.

With the field being there with no values you are telling SQL that you want to force a null into the field, which, obviously, is not allowed.

Answer 2

You need to set up the column mappings. First call

sqlCopy.ColumnMappings.Clear();

Then call

sqlBulkCopy.ColumnMappings.Add("Status", "Status"); sqlBulkCopy.ColumnMappings.Add("Priority", "Priority");

This means the bulk copy will stop trying to insert into MyPrimaryKey column and will only insert into the status and Priority columns.