Insert multiple rows using SQL Merge statement when condition matched

Question

Have Table with Non Identity Primary key. so whenever we insert new record we have to pass Primary key also.

I have to insert into the above table when record is not matched using Merge statement. But the problem is i could not increment the Primary key for each insert. it it throwing can not insert duplicate in Primary key column. Please find the Sample Merge query below.

Is it possible to insert multiple rows by increment primary key.

MERGE DBO.Table1 T1
        USING (DBO.Table2 )T2
        ON (T1.ID = T2.ID)
        WHEN MATCHED
            THEN UPDATE SET 
            T1.CURVE = T2.CURVE
        WHEN NOT MATCHED
            THEN INSERT (ID, CURVE )
            Values ( T2.ID, T2.CURVE);

First, you MERGE on T2.ID but insert VALUES(T2.ProductID ..., is this a typo? If not, that could be your problem, this doesn't say anything about T2.ProductID already existing or not. Second, are you sure T2.ID (or ProductID) meets the requirements of a primary key (Unique, non-null)? — Robert Sheahan
@RobertSheahan Sorry its my mistake. it is T2.ID. Yes, if condition is Matched then matched rows are getting updated, but for those condition not matched rows, i'm getting can not insert duplicate error. Hope i answered your query. Please let me know if you need more detail. — Rajkumar Selvara

Robert Sheahan Robert Sheahan · Accepted Answer · 2020-01-16T15:25:13

The code in your example works, here's a demo easy to reproduce

;with cteT as ( SELECT * FROM (VALUES (1,'T1 Val 1') , (2,'T1 Val 2') , (4,'T1 Val 4') ) as T1(ID, Curve)
)SELECT * INTO #Dest FROM cteT

;with cteT as ( SELECT * FROM (VALUES (3,'T2 Val 3') , (4,'T2 Val 4') , (5,'T2 Val 5') ) as T1(ID, Curve)
)SELECT * INTO #Srce FROM cteT;

MERGE INTO #Dest as T1
USING #Srce as T2 ON T1.ID=T2.ID
    WHEN MATCHED THEN UPDATE SET T1.Curve=T2.CURVE
    WHEN NOT MATCHED BY TARGET 
        THEN INSERT (ID, Curve) VALUES(T2.ID, T2.Curve)
;

SELECT * FROM #Dest ORDER BY ID

DROP TABLE #Dest
DROP TABLE #Srce

This is the output, note that for 1 & 2 the value is unchanged, for 4 it's updated from T2, and for 3 and 5 it's inserted from T2.

ID  Curve
1   T1 Val 1
2   T1 Val 2
3   T2 Val 3
4   T2 Val 4
5   T2 Val 5

This means that most likely either the problem is in your values for T2.ID or your sample is too pared down and misses a complication in your actual code. I'd start by checking your data in T2.ID

SELECT ID, COUNT(ID) as IDCount FROM DBO.Table2 GROUP BY ID HAVING COUNT(*) > 1
SELECT * FROM DBO.Table2 WHERE ID IS NULL

and see if any records turn up. If those are both empty, look at your actual merge code and see how it might differ from what you posted. If you post updated sample code I'll try to have a look.

Insert multiple rows using SQL Merge statement when condition matched

1 Answers