Executing merge statement via C# SqlCommand not working

Question

I am making my first attempt at using a temp table and a MERGE statement to update a SQL table via a SqlCommand object in C#. The program I'm working on is designed to first export a very large set of records (upwards of 20k+) into an excel spreadsheet. The user then has the ability to do a search and replace for a specific value and update as many fields in as many records as they like.

What I'm trying to do is then take that spreadsheet, populate a DataTable with it, and then populate a temporary SQL table with the DataTable using SqlBulkCopy.

I then use a MERGE statement to update the rows if they still exist in the database.

However, the problem I'm having is a Unique Constraint I have on the ZipCodeTerritory table keeps being triggered giving me the following error message:

Cannot insert duplicate key row in object 'dbo.ZipCodeTerritory' with unique index 'UQ_ChannelStateEndDateZipCodeISNULL'. The duplicate key value is (9, CA , 94351 , 9999-12-31).

This leads me to believe that somehow either the UPDATE statement isn't being executed or I have somehow joined the tables incorrectly in the part of the statement using the ON keyword. The unique constraint is only triggered during INSERT statements or an UPDATE to the ChannelCode, StateCode, ZipCode or EndDate fields. I am doing a mass update to the IndDistrnId field and have thoroughly checked the spreadsheet.

Again, this is my first attempt at trying this technique so any help/suggestions would be greatly appreciated. Thanks

C#

private static string updateCommand = "UPDATE SET Target.ChannelCode = Source.ChannelCode, Target.DrmTerrDesc = Source.DrmTerrDesc, Target.IndDistrnId = Source.IndDistrnId," +
                                            "Target.StateCode = Source.StateCode, Target.ZipCode = Source.ZipCode, Target.EndDate = Source.EndDate, Target.EffectiveDate = Source.EffectiveDate," +
                                            "Target.LastUpdateId = Source.LastUpdateId, Target.LastUpdateDate = Source.LastUpdateDate, Target.ErrorCodes = Source.ErrorCodes," +
                                            "Target.Status = Source.Status ";

//Load updates into datatable
DataTable table = LoadData(updates);

//Script to create temp table
string tmpTable =   "CREATE TABLE [dbo].[ZipCodeTerritoryTemp]( " +
                    "[ChannelCode] [char](1) NOT NULL, " +
                    "[DrmTerrDesc] [nvarchar](30) NOT NULL, " +
                    "[IndDistrnId] [char](3) NULL, " +
                    "[StateCode] [char](3) NOT NULL, " +
                    "[ZipCode] [char](9) NULL, " +
                    "[EndDate] [date] NOT NULL, " +
                    "[EffectiveDate] [date] NOT NULL, " +
                    "[LastUpdateId] [char](8) NULL, " +
                    "[LastUpdateDate] [date] NULL, " +
                    "[Id] [int] IDENTITY(1,1) NOT NULL, " +
                    "[ErrorCodes] [varchar](255) NULL, " +
                    "[Status] [char](1) NULL, " +
                    "CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED  " +
                "( " +
                    "[Id] ASC " +
                ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] " +
                ") ON [PRIMARY]";

using (SqlConnection connection = new SqlConnection(connString))
{
    connection.Open();

    //Create temp table
    SqlCommand cmd = new SqlCommand(tmpTable, connection);
    cmd.ExecuteNonQuery();

    try
    {

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            //Write to temp table
            bulkCopy.DestinationTableName = "ZipCodeTerritoryTemp";
            bulkCopy.WriteToServer(table);

            //Merge changes in temp table with ZipCodeTerritory
            string mergeSql = "merge ZipCodeTerritory as Target " +
                                "using ZipCodeTerritoryTemp as Source " +
                                "on " +
                                "Target.Id = Source.Id " +
                                "when matched then " +
                                updateCommand + ";";

            cmd.CommandText = mergeSql;
            int results = cmd.ExecuteNonQuery();

            //Drop temp table
            cmd.CommandText = "DROP TABLE [dbo].[ZipCodeTerritoryTemp]";
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        //Drop temp table
        SqlCommand final = new SqlCommand("DROP TABLE [dbo].[ZipCodeTerritoryTemp]", connection);
        final.ExecuteNonQuery();
    }
}

SQL

For readability's sake here's the MERGE statement as I wrote it in SQL Server Management Studio. I copied this into the C#. FYI - ran this statement in Management Studio and received the exact same error message.

MERGE INTO ZipCodeTerritory as Target
USING ZipCodeTerritoryTemp as Source
ON Target.Id = Source.Id

WHEN MATCHED THEN

UPDATE SET Target.ChannelCode = Source.ChannelCode, Target.DrmTerrDesc = Source.DrmTerrDesc, Target.IndDistrnId = Source.IndDistrnId,
    Target.StateCode = Source.StateCode, Target.ZipCode = Source.ZipCode, Target.EndDate = Source.EndDate, Target.EffectiveDate = Source.EffectiveDate,
    Target.LastUpdateId = Source.LastUpdateId, Target.LastUpdateDate = Source.LastUpdateDate, Target.ErrorCodes = Source.ErrorCodes,
    Target.Status = Source.Status;

Side-note: you don't need all those `, " +` at the end of the line. You can use a [verbatim string literal](http://msdn.microsoft.com/en-us/library/aa691090(v=vs.71).aspx) which begins with `@`. Then you can copy/paste the sql directly into visual studio. — Tim Schmelter, Jan 16 '14 at 21:09
I would try to remove the DROP of the temporary table and check what is really inserted there. Is it possible that the import from the excel sheet loads some bogus row? — Steve, Jan 16 '14 at 21:47
I stopped the code while it was running to check the data in the temp table. It is all there, imported from the spreadsheet, with the updates to the `IndDistrnId` field. Right now I'm experimenting in SQL Server Management Studio with the temp table/data trying to get the `MERGE` to work. — NealR, Jan 16 '14 at 21:51
The exception talks about an unique index (composed by many columns) on the __main table__ that is violated. However, to find the matches between the two tables, you use a different key. Have you checked if the imported row with the values `(9, CA , 94351 , 9999-12-31)` doesn't conflict with a different row in the main table with the same value on the unique index? — Steve, Jan 16 '14 at 21:57
I just realized that the `IDENTITY(1,1,)` property was being set on the temp table, so the `Id` fields can't match up. Checking to see if that was the (only..) problem. — NealR, Jan 16 '14 at 22:00

score 3 · Accepted Answer · answered Jan 16 '14 at 22:14

The issue wound up being the IDENTITY property being set on the Id field in the temp table. After removing this I was able to run the MERGE without error. Here's the temp table now:

//Script to create temp table
string tmpTable =   "CREATE TABLE [dbo].[ZipCodeTerritoryTemp]( " +
                    "[ChannelCode] [char](1) NOT NULL, " +
                    "[DrmTerrDesc] [nvarchar](30) NOT NULL, " +
                    "[IndDistrnId] [char](3) NULL, " +
                    "[StateCode] [char](3) NOT NULL, " +
                    "[ZipCode] [char](9) NULL, " +
                    "[EndDate] [date] NOT NULL, " +
                    "[EffectiveDate] [date] NOT NULL, " +
                    "[LastUpdateId] [char](8) NULL, " +
                    "[LastUpdateDate] [date] NULL, " +
                    "[Id] [int] NOT NULL, " +               //DO NOT GIVE THE PK OF THE TEMP TABLE AN IDENTITY(1,1,) PROPRETY
                    "[ErrorCodes] [varchar](255) NULL, " +
                    "[Status] [char](1) NULL, " +
                    "CONSTRAINT [PK_ZipCodeTerritoryTemp] PRIMARY KEY NONCLUSTERED  " +
                "( " +
                    "[Id] ASC " +
                ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] " +
                ") ON [PRIMARY]";

You don't really need the `ID` column and the `PK` too. It only depends on how you set the `MERGE` command `MATCH` conditions. — Cheshire Cat, Apr 07 '21 at 14:58

Executing merge statement via C# SqlCommand not working

1 Answers1

Linked