How is a Table Valued Parameter passed into Merge statement getting Duplicate Key error on an empty table?

Question

I am sending a Table Valued Parameter into an MS SQL Server 2008 R2 stored procedure from C# code. The error I receive is: "Violation of PRIMARY KEY constraint 'PK_Example.ExampleTable'. Cannot insert duplicate key in object 'Example.ExampleTable'. The statement has been terminated."

Table structure: Example.ExampleTable

[ID] (PK, int, not null)
[Month] (PK, int, not null)
[Year] (PK, int, not null)
[ExternalTextReference] (varchar(150), null)
[UserNote] (varchar(MAX), null)
[CheckedComplete] (bit, not null)

PK is the only constraint.

Stored Procedure:

ALTER PROCEDURE [dbo].[ExampleSaveGridCheckBatch]
@IDs IDLIST readonly,
@year int = 0,
@month int = 0,
@checked bit = 0
AS
BEGIN
     SET NOCOUNT ON;
     MERGE INTO [Example].[ExampleTable] AS Target 
     USING ( select * from @IDs )
            AS Source (ID)
     ON Target.ID = Source.ID
     WHEN MATCHED THEN
          UPDATE SET CheckedComplete = @checked
     WHEN NOT MATCHED BY TARGET THEN
          INSERT ([ID], [Year], [Month], [CheckedComplete]) 
          VALUES (Source.ID, @year, @month, @checked);
END

As for the IDLIST part above: CREATE TYPE IDLIST AS TABLE (n int);

C# Code:

DataTable table = new DataTable("IDsList");            
DataColumn col1 = new DataColumn("ID", System.Type.GetType("System.Int32"));
table.Columns.Add(col1);

var cn = new SqlConnection();
createConnection(cn);

SqlCommand cmd = new SqlCommand("ExampleSaveGridCheckBatch", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@month", month);
cmd.Parameters.AddWithValue("@checked", checkstatus);
SqlParameter sparam = new SqlParameter("@IDs", SqlDbType.Structured);
foreach (int item in iDsList)
{
    table.Rows.Add(item);
}
sparam.Value = table;
cmd.Parameters.Add(sparam);

cmd.ExecuteNonQuery();

On execution, I get the error. In an example set, the variables are as follows: year: 2011 month: 11 checkstatus: 1 iDsList: (List of 71 unique integers)

The intent of my function is that someone clicks "check all" on a grid and I save the checked status to each of the affected rows in the database without going through each one. This is an ancillary table created by normalization, so there may or may not be a record for each, hence the merge. I got the error and decided to simplify the problem by truncating the table and trying again, but even with an absolutely empty table I get the "duplicate key" error.

So the core question is how I can have a duplicate key error with no records, and how do I fix it so I don't get the error anymore?

Are you sure that the list of 71 integers are in fact unique?, have you tried with a smaller list?. What happens if you change your sp and put this: ` USING ( select DISTINCT ID from @IDs ) AS Source (ID)`? — Lamak, Dec 15 '11 at 18:09
I am certain. The 71 are pulled from another table that enforces the unique constraint, and I can visually verify them as unique. — Merennulli, Dec 15 '11 at 20:15
The Distinct fixed it and revealed the problem. I don't yet know why, but it was adding a 0 ID double along with each record. Still not sure why I got a duplicate error with no rows recorded. This does fix the actual problem portion of the question, the "why that error?" part is academic, though I would like to know. — Merennulli, Dec 15 '11 at 20:17
Then it means that the list of ids that you were retrieving from your query (`SELECT Id FROM @IDs`) were not unique. So even if your table was empty, you were still trying to insert duplicate records. Since your query failed, it rolled back and didn't actually inserted any rows — Lamak, Dec 15 '11 at 21:43

score 1 · Accepted Answer · answered Dec 17 '11 at 06:19

1

In Example Table Month and Year Columns are also marked as Primary Key along with ID and i guess they do not contain Unique Values. Remove the Primary Key Constraints from Month and YEAR Columns in the table and try again.

answered Dec 17 '11 at 06:19

Ankit

680
4
17

Actually, the question and problem were answered by Lamak in the comments above rather than in an answer. This is sort of sidestepping the problem and would have resulted in duplicates, though it would have revealed the problem (the magic zero-ID rows being injected by a badly coded DevExpress product we use). In lieu of any way to mark Lamak's comments as an answer, I'll mark this one. – Merennulli Dec 21 '11 at 16:23

How is a Table Valued Parameter passed into Merge statement getting Duplicate Key error on an empty table?

1 Answers1