Is there any way to make a idempotent insert of three tables in a single executable script?

Question

I'm currently working in a .net app that uses a script to refresh all the database when you checkout to a different git branch. The case is when this script 'refreshes' and cleans all the old test data from that branch also execute all the migration files from a folder named 'migrations'. So i'm trying to create a migration to insert some specific values in some tables using idempotence. This migration file must be like the others inside the 'migrations' folder (idempotent) in order to be executed every time the script is executed, so that it does not duplicate the data, keep the structure and be executed multiple times and leave the database in the same state.

The inserts are:

USE [$defaultDB]
GO

INSERT INTO [dbo].[Catalog]
           ([name]
           ,[description]
           ,[createdBy]
           ,[createdOn]
           ,[createdIn]
           ,[modifiedBy]
           ,[modifiedOn]
           ,[modifiedIn]
           ,[isActive]
           ,[catalogTypeID])
     VALUES
           ('PolicyClasificationID',
            'Id de clasificación de póliza',
            2,
            2022-01-20 13:43:09.687,
            '127.0.0.1',
            2,
            2022-01-20 13:43:09.687,
           '127.0.0.1',
            1,
            1,
       )

GO

INSERT INTO [dbo].[CatalogItem]
           ([catalogID]
           ,[catalogFlavorID]
           ,[parentCatalogItemID]
           ,[name]
           ,[displayLabel]
           ,[description]
           ,[catalogValue]
           ,[sequence]
           ,[createdBy]
           ,[createdOn]
           ,[createdIn]
           ,[modifiedBy]
           ,[modifiedOn]
           ,[isActive]
           ,[parentCatalogID]
           ,[parentCatalogValueID])
     VALUES

(271, 1,    NULL, 'Fidem', 'FIDEM', NULL,   1,  1,  2,  '2022-01-20 12:41:12.203', '127.0.0.1', 2,  '2022-01-20 12:41:12.203',  1,  NULL,   NULL) 
(271, 1,    NULL, 'Bac', 'BAC', NULL,   2,  2,  2,  '2022-01-21 12:41:12.203', '127.0.0.1', 2,  '2022-01-21 12:41:12.203',  1,  NULL,   NULL) 

GO

INSERT INTO [dbo].[CompanyComponentFlavor]
           ([companyID]
           ,[componentID]
           ,[subComponentID]
           ,[flavorID])
     VALUES
           (3,
            7,
            271,
            1)

END

Any help would be appreciated.

Answer 1

Not a problem. Let's use your CatalogItem table as the example as you're trying to insert multiple rows and so conceptually it's the most challenging. But, as you'll see, it's not that challenging.

MERGE [dbo].[CatalogItem] AS tgt
USING (
    SELECT *
    FROM (VALUES
        (271, 1,    NULL, 'Fidem', 'FIDEM', NULL,   1,  1,  2,  '2022-01-20 12:41:12.203', '127.0.0.1', 2,  '2022-01-20 12:41:12.203',  1,  NULL,   NULL),
        (271, 1,    NULL, 'Bac', 'BAC', NULL,   2,  2,  2,  '2022-01-21 12:41:12.203', '127.0.0.1', 2,  '2022-01-21 12:41:12.203',  1,  NULL,   NULL) 
    ) as x(
        [catalogID]
        ,[catalogFlavorID]
        ,[parentCatalogItemID]
        ,[name]
        ,[displayLabel]
        ,[description]
        ,[catalogValue]
        ,[sequence]
        ,[createdBy]
        ,[createdOn]
        ,[createdIn]
        ,[modifiedBy]
        ,[modifiedOn]
        ,[isActive]
        ,[parentCatalogID]
        ,[parentCatalogValueID]
    )
) AS src
    ON src.[catalogID] = tgt.[catalogID]
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
            [catalogID]
            ,[catalogFlavorID]
            ,[parentCatalogItemID]
            ,[name]
            ,[displayLabel]
            ,[description]
            ,[catalogValue]
            ,[sequence]
            ,[createdBy]
            ,[createdOn]
            ,[createdIn]
            ,[modifiedBy]
            ,[modifiedOn]
            ,[isActive]
            ,[parentCatalogID]
            ,[parentCatalogValueID]
        )
    VALUES (
        src.[catalogID]
        ,src.[catalogFlavorID]
        ,src.[parentCatalogItemID]
        ,src.[name]
        ,src.[displayLabel]
        ,src.[description]
        ,src.[catalogValue]
        ,src.[sequence]
        ,src.[createdBy]
        ,src.[createdOn]
        ,src.[createdIn]
        ,src.[modifiedBy]
        ,src.[modifiedOn]
        ,src.[isActive]
        ,src.[parentCatalogID]
        ,src.[parentCatalogValueID]
    );

There are basically two things going on here. The first is I'm using a MERGE statement. It allows for "insert if not exists" type operations (among other things). It gets a bad reputation for some odd behaviors. But for the use case you're describing (i.e. paving over a nuked dev environment) it feels like a good fit. All this MERGE statement says is "if a given row is missing in the target table (as identified by the catalogID), insert it".

The second thing that you may not be used to seeing is the use of a row valued constructor as the source of the data. It's just a trick I picked up several years ago and I tend to use it when making a temporary table doesn't make sense.

One last thing that I'm showing here is that you may or may not want an "all or nothing" insert of missing data across all three of your tables. If so, wrap everything that you want to execute as a unit in a transaction by putting "BEGIN TRAN/COMMIT" at the begin/end (respectively). That feels like overkill to me for this situation though.