I am able to execute my storeproc. When I execute it second time instead of updating the existing values same values from source are inserted as new values. i.e my target has 1 2 3 When I run the store proc sec time instead of updating 1,2,3 it is inserting the same 1 2 3 1 2 3 My condition for when matched then select S.REPORT_TEST1 except T.REPORT_TEST1 is not working. When I use the same code on a different table which doesn't have data conversions I am able to update. Can anyone tell where am I going wrong.
CREATE PROCEDURE [dbo].[Merge]
INSERT INTO .[dbo].[TARGET]
( REPORT_TEST1
,REPORT_TEST2
,REPOST_TEST3
FROM
(
MERGE [dbo].[TARGET] T
USING
(
SELECT
Cast([REPORT TEST1] as int) [REPORT_TEST1]
,Cast([REPORT TEST2] as int) [REPORT_TEST2]
,Cast([REPORT TEST3] as int) [REPORT_TEST3]
FROM [dbo].[SOURCE]
) S
ON
(T.[REPORT_TEST1]=S.[REPORT_TEST1]
)
WHEN NOT MATCHED BY TARGET
THEN INSERT
VALUES (
S.REPORT_TEST1
,S.REPORT_TEST2
,S.REPOST_TEST3
)
WHEN MATCHED
AND EXISTS
(
SELECT
S.REPORT_TEST1
,S.REPORT_TEST2
S.REPOST_TEST3
EXCEPT
SELECT
T.REPORT_TEST1
,T.REPORT_TEST2
,T.REPOST_TEST3
)
OUTPUT $ACTION ACTION_OUT
S.REPORT_TEST1
,S.REPORT_TEST2
,S.REPOST_TEST3
)
;
Thanks
Aucun commentaire:
Enregistrer un commentaire