CODEWITHSUNDEEP
sql-server-2008merge
Swicky
Swicky

Reputation: 73

Why would a SQL MERGE have a duplicate key error, even with HOLDLOCK declared?

There is a lot of information that I could find on SQL Merge, but I can't seem to get this working for me. Here's what's happening.

Each day I'll be getting an Excel file uploaded to a web server with a few thousand records, each record containing 180 columns. These records contain both new information which would have to use INSERT, and updated information which will have to use UPDATE. To get the information to the database, I'm using C# to do a Bulk Copy to a temp SQL 2008 table. My plan was to then perform a Merge to get the information into the live table. The temp table doesn't have a Primary Key set, but the live table does. In the end, this is how my Merge statement would look:

MERGE Table1 WITH (HOLDLOCK) AS t1
USING (SELECT * FROM Table2) AS t2
ON t1.id = t2.id
WHEN MATCHED THEN
    UPDATE SET (t1.col1=t2.col1,t1.col2=t2.col2,...t1.colx=t2.colx)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (col1,col2,...colx)
    VALUES(t2.col1,t2.col2,...t2.colx);

Even when including the HOLDLOCK, I still get the error Cannot insert duplicate key in object. From what I've read online, HOLDLOCK should allow SQL to read primary keys, but not perform any insert or update until after the task has been executed. I'm basically learning how to use MERGE on the fly, but is there something I have to enable for SQL 2008 to pick up on MERGE Locks?

Upvotes: 4

Views: 5354

Answers (1)

Swicky
Swicky

Reputation: 73

I found a way around the problem and wanted to post the answer here, in case it helps anyone else. It looks like MERGE wouldn't work for what I needed since the temporary table being used had duplicate records that would be used as a Primary Key in the live table. The solution I came up with was to create the below stored procedure.

    -- Start with insert
    INSERT INTO LiveTable(A, B, C, D, id)
    (
    -- Filter rows to get unique id
    SELECT A, B, C, D, id FROM(
        SELECT A, B, C, D, id,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_number
        FROM TempTable
        WHERE NOT EXISTS(
    SELECT id FROM LiveTable WHERE LiveTable.id = TempTable.id)
        ) AS ROWS
    WHERE row_number = 1
    )

-- Continue with Update
-- Covers skipped id's during insert
UPDATE tb_TestMLS
SET
    LiveTable.A = T.A,
    LiveTable.B = T.B,
    LiveTable.C = T.C,
    LiveTable.D = T.D
FROM LiveTable L
INNER JOIN
    TempTable T
ON
    L.id= T.id

Upvotes: 2

Related Questions