SqlBulkCopy Violates Unique Constraint with Wrong Value

Question

I have a table that looks like:

CREATE TABLE [dbo].[Site]
(
    [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
    [Name] NVARCHAR(MAX) NOT NULL, 
    [CreatedAt] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    [UpdatedAt] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    [DeletedAt] DATETIME NULL, 
    CONSTRAINT [AK_Site_Name] UNIQUE ([Name])
)

I am loading data from a file, parsing it and manipulating it and uploading it back. This occurs from within a datatable that looks like so:

Name | CreatedAt | UpdatedAt

I create this in a loop after reading in the file like so:

DataTable dt = new DataTable() { TableName = "Site" };

var dc = new DataColumn("Name", typeof(string)) { Unique = True };
dt.Columns.Add(dc);

dc = new DataColumn("CreatedAt", typeof(DateTime));
dt.Columns.Add(dc);

dc = new DataColumn("UpdatedAt", typeof(DateTime));
dt.Columns.Add(dc);

foreach(var rec in myFile) {
    var dr = dt.NewRow();
    dr[0] = rec.SiteName;
    dr[1] = DateTime.UtcNow;
    dr[2] = DateTime.UtcNow;
    dt.Rows.Add(dr);
}

And finally passing it into SqlBulkCopy like so:

var connection = new SqlConnection(ConnectionString);

        var bulkCopy =
            new SqlBulkCopy
                (connection,
                    SqlBulkCopyOptions.TableLock |
                    SqlBulkCopyOptions.FireTriggers |
                    SqlBulkCopyOptions.UseInternalTransaction,
                    null
                )
            {
                DestinationTableName = "Site",
                BatchSize = 1000
            };
        connection.Open();
        bulkCopy.WriteToServer(dataTable);
        connection.Close();

I get an exception saying:

Violation of UNIQUE KEY constraint 'AK_Site_Name'. Cannot insert duplicate key in object 'dbo.Site'. The duplicate key value is (11/21/2018 5:52:13 AM).

Inspecting my datatable in the debugger shows the columns are correct. There are no errors coming from the datatable itself.

I attempted to reproduce this in SSMS as follows:

DECLARE @currentd DATETIME = CURRENT_TIMESTAMP;

INSERT INTO [dbo].[Site] (Name, CreatedAt, UpdatedAt) VALUES ('Test', @currentd, @currentd), ('Test', @currentd, @currentd)

And of course, as expected, it worked fine. This leads me to believe somehow a date is getting put in for the Name field but im not sure how.

Has anyone experienced this and can point me in the right direction?

@JohnB How is that possible? I am setting unique on the datatable, and the violating value is a datetime? — CL40
ok, in the debugger just double check that columns in your dr variable are as you expect - eg is dr[0] really site name etc. — Jazb
@JohnB @Disaffected1070452 I am using CsvHelper. I have verified each row in the datatable is of the correct data type — CL40

Elyas Esna Elyas Esna · Accepted Answer · 2018-11-21T06:18:22

dr[0] is for column Id which should be set to null so the final loop should be like:

foreach(var rec in myFile) {
 var dr = dt.NewRow();
 dr[0] = null;
 dr[1] = rec.SiteName;
 dr[2] = DateTime.UtcNow;
 dr[3] = DateTime.UtcNow;
 dt.Rows.Add(dr); }

SqlBulkCopy Violates Unique Constraint with Wrong Value

1 Answers