ALTER PROC TestProc

AS

BEGIN

    --打开完全事物回滚

    SET XACT_ABORT ON;

    SET NOCOUNT ON;

    --开始异常拦截

    BEGIN TRY

        --开始事物

        BEGIN TRANSACTION;

        PRINT '开始同步数据';

        MERGE INTO dbo.TestTable AS T

        USING

        (

            SELECT ROW_NUMBER() OVER (ORDER BY CREATE_DATE DESC) AS Id,

                   NAME,

                   PHONE

            FROM [TEST_OGU].[Hyx_Finance_DB].[dbo].[TBL_CUSTOMER_RELATIONSHIP]

        ) AS d

        ON T.Name = d.NAME

           AND T.Phone = d.PHONE

        --是否存在(存在更新)

        WHEN MATCHED THEN

            UPDATE SET Name = d.NAME,

                       Phone = d.PHONE

        --不存在数据（新增数据）

        WHEN NOT MATCHED THEN

            INSERT

            (

                Name,

                Phone

            )

            VALUES

            (d.NAME, d.PHONE);

        PRINT '同步数据结束';

        --提交事物

        COMMIT TRANSACTION;

    --结束异常拦截

    END TRY

    BEGIN CATCH

        --回滚事物

        IF XACT_STATE() <> 0

        BEGIN

            ROLLBACK TRANSACTION;

        END;

    END CATCH;

    --还原事物设置

    SET XACT_ABORT OFF;

END;