Update statement of Merge query is not working

102 Views Asked by At

I have two tables Application and Application_temp . I wrote a merge query to merge data from application_temp to application. But Update statement is not working. Query is inserting the whole set of records again instead of updating , I doubt it must be because date columns, It would be great if anyone could help me with this.

I tried multiple sources from google, nothing helped. Tried changing various match conditions but din't work.

CREATE PROCEDURE [dbo].[MergeApplication_tempToApplication]
AS
--CREATE UNIQUE INDEX cmdbid ON Application_Temp(cmdb_id) WITH 
(IGNORE_DUP_KEY = OFF)

MERGE INTO Application AS Target
USING Application_temp AS Source
    ON (
            --Target.CMDB_ID=Source.CMDB_ID
            --AND Target.Application_Name=Source.Application_Name
            Target.maas_application_id = source.maas_application_id
            )
WHEN MATCHED
    AND (
        Target.CMDB_ID <> Source.CMDB_ID
        OR Target.Application_Name <> Source.Application_Name
        OR Target.Fss_Portfolio <> Source.Fss_Portfolio
        OR Target.Managed_By <> Source.Managed_By
        --Target.Date_Created <> Source.Date_Created OR
        --Target.Date_Updated <> Source.Date_Updated
        )
    THEN
        UPDATE
        SET Target.cmdb_id = Source.cmdb_id
            ,Target.Application_Name = Source.Application_Name
            ,Target.Fss_Portfolio = Source.Fss_Portfolio
            ,Target.Managed_By = Source.Managed_By
            ,Target.Date_Created = Source.Date_Created
            ,Target.Date_Updated = Source.Date_Updated
WHEN NOT MATCHED
    THEN
        INSERT (
            Application_Name
            ,cmdb_id
            ,Fss_Portfolio
            ,Managed_By
            ,Date_Created
            ,Date_Updated
            )
        VALUES (
            source.Application_Name
            ,source.cmdb_id
            ,source.Fss_Portfolio
            ,source.Managed_By
            ,source.Date_Created
            ,source.Date_Updated
            );

As I said update statement is not working. Can any expert in SQL Server. Help me with this? I have struggling from past month.

0

There are 0 best solutions below