1

I am currently doing a merge on the following table between my test_db (target) and the same table on a different database called tablebackups (source).

    IF OBJECT_ID('[dbo].[tblBCatalogueType]') IS NOT NULL 
    DROP TABLE [dbo].[tblBCatalogueType] 
    GO
    CREATE TABLE [dbo].[tblBCatalogueType] ( 
    [sintCatalogueTypeID]  SMALLINT         IDENTITY(1,1)   NOT NULL,
    [blnIsCurrent]         BIT                              NOT NULL,
    [tsRowVersion]         TIMESTAMP                        NOT NULL,
    CONSTRAINT   [PK_tblProdCatalogueType]  
PRIMARY KEY CLUSTERED    ([sintCatalogueTypeID] asc) 
WITH FILLFACTOR = 97)

When I have a look at the tables that would be referencing my tlbBCatalogueType table I get the following list:

script to find foreign key constraints:

----------------------------------------------------------------------------
declare @referenced_table sysname 
declare @ref_Obj int 

declare @table sysname 
declare @Obj int 

select @table            = 'dbo.tblBCatalogueType'   --'tblBCataloguePriceSetItem'   
select @referenced_table = 'dbo.tblBCatalogueType'   --'dbo.tblBCataloguePriceSet'   

select @obj     = OBJECT_ID(@table)
select @ref_obj = OBJECT_ID(@referenced_table)

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    sch.name AS [referenced_schema],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas sch2
    ON tab2.schema_id = sch2.schema_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where 1=1
  AND (     ( @OBJ IS NULL OR @obj = tab1.object_id) 
         OR ( @ref_obj IS NULL OR @ref_obj = tab2.object_id )) 

enter image description here

this is my merge statement:

    SET NOCOUNT ON;

    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;

    --  SELECT @@TRANCOUNT,XACT_STATE();

    IF @TranCountAtStart = 0
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION USP_Procedure_Name;

     BEGIN TRY
-------------------------------------------------------------------------------------------         

-- the dbo.tblBCataloguePriceSet must be run before this merge.

-- Message: The MERGE statement conflicted with the REFERENCE constraint "fk_CataloguePriceSet_CatalogueType". 
-- The conflict occurred in database "Bocss2", table "dbo.tblBCataloguePriceSet", column 'sintCatalogueTypeID'.  





                IF OBJECT_ID( 'tablebackups.dbo.tblBCatalogueType_log') IS NOT NULL DROP TABLE tablebackups.dbo.tblBCatalogueType_log;

                CREATE TABLE tablebackups.dbo.tblBCatalogueType_log(
                  ChangeType         NVARCHAR(10)
                 ,sintCatalogueTypeID        SMALLINT NOT NULL
                 ,DateTimeChanged    DateTime NOT NULL);



                BEGIN TRANSACTION T1

                SET IDENTITY_INSERT dbo.tblBCatalogueType  ON;

                SELECT @@TRANCOUNT
                SELECT XACT_STATE()

                MERGE dbo.tblBCatalogueType  AS TARGET
                USING tablebackups.dbo.tblBCatalogueType AS SOURCE
                   ON TARGET.sintCatalogueTypeID = SOURCE.sintCatalogueTypeID

                WHEN MATCHED

                            THEN UPDATE SET 
                                  [blnIsCurrent] = SOURCE.blnIsCurrent

                WHEN NOT MATCHED BY TARGET


                             THEN INSERT(   [sintCatalogueTypeID]
                                           ,[blnIsCurrent]
                                )
                                VALUES( 
                                            SOURCE.[sintCatalogueTypeID]
                                           ,SOURCE.[blnIsCurrent]
                                        )

                WHEN NOT MATCHED BY SOURCE

                            THEN DELETE 
                --$action specifies a column of type nvarchar(10) 
                --in the OUTPUT clause that returns one of three 
                --values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
                --according to the action that was performed on that row
                -------------------------------------
                OUTPUT
                   $ACTION ChangeType,
                   coalesce (inserted.sintCatalogueTypeID, deleted.sintCatalogueTypeID) sintCatalogueTypeID,
                   Getdate () DateTimeChanged
                    INTO tablebackups.dbo.tblBCatalogueType_log
                -------------------------------------
                ;


                SELECT @@ROWCOUNT;

                SET IDENTITY_INSERT dbo.tblBCatalogueType  OFF;

                COMMIT TRANSACTION T1 

-------------------------------------------------------------------------------------------


    END TRY

    BEGIN CATCH

    SET IDENTITY_INSERT dbo.tblBCatalogueType  OFF;

    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY   INT,
            @ERRORNUMBER     INT,
            @ERRORSTATE      INT,
            @ERRORPROCEDURE  SYSNAME,
            @ERRORLINE       INT,
            @XASTATE         INT

    SELECT
            @ERRORMESSAGE     = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER      = ERROR_NUMBER(),
            @ERRORSTATE       = ERROR_STATE(),
            @ERRORPROCEDURE   = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT                    CHAR(13) +
      'Message:'         +    SPACE(1) + @ErrorMessage                           + SPACE(2) + CHAR(13) +
      'Error:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber)      + SPACE(1) + CHAR(13) +
      'Severity:'        +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity)    + SPACE(1) + CHAR(13) +
      'State:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState)       + SPACE(1) + CHAR(13) +
      'Routine_Name:'    +    SPACE(1) + coalesce(@ErrorProcedure,'')            + SPACE(1) + CHAR(13) +
      'Line:'            +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine)        + SPACE(1) + CHAR(13) +
      'Executed As:'     +    SPACE(1) + SYSTEM_USER + SPACE(1)                             + CHAR(13) +
      'Database:'        +    SPACE(1) + DB_NAME() + SPACE(1)                               + CHAR(13) +
      'OSTime:'          +    SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121)        + CHAR(13) 
    )

        SELECT @XASTATE = XACT_STATE();

        IF @XASTATE = - 1
            ROLLBACK;

        IF @XASTATE = 1
            AND @TranCountAtStart = 0
            ROLLBACK

        IF @XASTATE = 1
            AND @TranCountAtStart > 0
            ROLLBACK TRANSACTION USP_Procedure_Name;
            --We can also save the error details to a table for later reference here.
            RAISERROR (@ERRORMESSAGE,16,1)

    END CATCH

How could I add a error-handling process to my merge?

Most probably it breaks on the delete.

       WHEN NOT MATCHED BY SOURCE

                    THEN DELETE 

Here is where I should have something to handle it.

Let's keep it simple, and just say - if it breaks by a constraint violation - just don't delete the record.

How can I get this done?

1

Well, "just don't delete the record" aside, you can change the foreign keys to be on delete cascade or on delete set null. This will delete everything when you delete the parent, or set the child keys to null, respectively.

Those aren't always great behaviours. For example if a customer deleted their account you probably don't want to drop all their order history and tax invoices as well.

An ugly, hideous, but common alternative, is:

  • If you know the schema isn't going to change (or you'll be the one doing it), you can just include a sub-query which will exclude those keys that you don't want to delete. This isn't too bad.

  • If you wanted to get really ugly, you can use your procedure there to create dynamic SQL to do the same (putting it into a temp table and then excluding that from your results). Don't do this unless you have to.

  • You could use some combination of these with an [instead of delete] trigger. This is probably the worst of all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.