Why "merge delete" deleting entire target table in SQL Server
I was writing a stored procedure upsert on a table.
Here is my stored procedure:
create PROCEDURE [dbo].[ABC]
(
@abc [dbo].[ABCTable] readonly
)
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO dbo.ABC trg
USING @abc src
ON src.a = trg.a
and src.b=trg.b
and src.c = trg.c
and src.d = trg.d
and src.e = trg.e
and src.f = trg.f
and src.g =trg.g
WHEN MATCHED
THEN
UPDATE
SET
h = src.h
WHEN Not MATCHED BY SOURCE
THEN
DELETE
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
a,
b,
c,
d,
e,
f,
g,
h
)
VALUES (
a,
b,
c,
d,
e,
f,
g
,h
);
END
Here is the data
ABC
a b c d e f g h
1 2 3 4 5 6 7 8
1 2 9 10 11 12 13 14
@abc
a b c d e f g h
3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 9
Final output as :
ABC
a b c d e f g h
1 2 3 4 5 6 7 9
1 2 9 10 11 12 13 14
3 4 5 6 7 8 9 10
However my stored procedure is deleting the existing rows and adding new one. How can I resolve this?
Comments
Post a Comment