jeudi 7 janvier 2016

merge Store proc with datatype conversions

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