Duplicate Key Error in SQL and Delete and Update

Question

I need to insert rows from select, if key is duplicate then delete entry and insert them

 Table1 

  ColumnA   ColumnB   ColumnC  ColumnD
   A          1        A1        7/21/2017
   B          2        B1        7/22/2017
   C           3       C1       7/23/2017

Unique Combination ColumnA and ColumnC

 Table2

  ColumnE  ColumnF ColumnG
    A        1      A1
    A         2     A2
    B         3     B1
    B         2     B2
    C         3     C1
    C         1     C2

I should insert the rows from table 2 to table 1

Insert into table1 (columnA, columnB, ColumnC) select columnE, ColumnF, ColumnG from table2

The above query gets conflicts saying duplicate keys are inserted but want a way to delete rows which creates conflicts and insert that row. final output should be

 Table 1
 ColumnA  ColumnB ColumnC  ColumnD  
    A        1      A1     08/08/2017  - deleted and added as conflict arised
    A         2     A2     08/08/2017
    B         3     B1     08/08/2017  -deleted and added as conflict arised
    B         2     B2     08/08/2017
    C         3     C1     08/08/2017    
    C         1     C2     08/08/2017

Merge is still unfortunately slow. a DI (Delete, then Insert) sequence will probably be better, although a UI (update, then insert) sequence would be best. — Laughing Vergil, Aug 08 '17 at 15:54
What is the column D in table 1? How do you determine the date? — Mislav Zic, Aug 08 '17 at 15:54
@LaughingVergil There is no UI interface to it, standalone stored procedure — TechJump, Aug 08 '17 at 15:57
@LaughingVergil was using the acronym "UI" to mean "Update/Insert", not User Interface. — Xedni, Aug 08 '17 at 16:23

Mislav Zic · Answer 1 · 2017-08-08T17:30:12.610

You don't have to delete then insert. Just update the ColumnD column (or all non-key columns) and insert new ones from the Table2. I would order statements so that firstly goes UPDATE and then INSERT, so that the UPDATE works on smaller set of original rows (before the insert). Here it is, instead of MERGE, just plain old UPDATE-INSERT:

DECLARE
    @Conflicted TABLE(
        ColumnA char(1),
        ColumnB int,
        ColumnC char(2),
        ColumnD date
    )

BEGIN TRAN

UPDATE dst
SET
    dst.ColumnD = GETDATE()
OUTPUT
    deleted.ColumnA,
    deleted.ColumnB,
    deleted.ColumnC,
    deleted.ColumnD
INTO
    @Conflicted
FROM
    Table1 AS dst
    JOIN Table2 AS src
    ON dst.ColumnA = src.ColumnE AND
       dst.ColumnB = src.ColumnF AND
       dst.ColumnC = src.ColumnG

INSERT INTO Table1(
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnD
)
SELECT
    t2.ColumnE,
    t2.ColumnF,
    t2.ColumnG,
    GETDATE()
FROM
    Table2 AS t2
WHERE
    NOT EXISTS(SELECT * FROM Table1 WHERE ColumnA = t2.ColumnE AND ColumnB = t2.ColumnF AND ColumnC = t2.ColumnG)

COMMIT TRAN

In the table variable @Conflicted now you have all conflicted rows from the Table1 which have been replaced with new ones from the Table2.

And here is a version with MERGE:

DECLARE
    @Updated TABLE(
        ColumnA char(1),
        ColumnB int,
        ColumnC char(2),
        ColumnD date,
        Action nvarchar(10)
    )

MERGE Table1 AS dst
USING (
    SELECT * FROM Table2
) as src
ON dst.ColumnA = src.ColumnE AND
   dst.ColumnB = src.ColumnF AND
   dst.ColumnC = src.ColumnG
WHEN NOT MATCHED THEN 
    INSERT(
        ColumnA,
        ColumnB,
        ColumnC,
        ColumnD
    ) VALUES (
        src.ColumnE,
        src.ColumnF,
        src.ColumnG,
        GETDATE()
    )
WHEN MATCHED THEN UPDATE
    SET
        dst.ColumnD = GETDATE()
OUTPUT
    deleted.ColumnA,
    deleted.ColumnB,
    deleted.ColumnC,
    deleted.ColumnD,
    $action AS action
INTO
    @Updated;

SELECT * FROM @Updated WHERE Action = 'UPDATE' -- List replaced rows from the Table1

Duplicate Key Error in SQL and Delete and Update

1 Answers1