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.

Does [Correct use of transactions in SQL Server](https://stackoverflow.com/questions/10153648/correct-use-of-transactions-in-sql-server) seem to be what you're looking for? — Andrew Morton, Jan 28 '22 at 21:29
What are the keys that *uniquely* identify the rows in each table? If you have no way of uniquely identifying rows you cannot make this idempotent — Charlieface, Jan 30 '22 at 00:59
The use of transactions i think it is something tricky for me, but yes that it is something like that i', looking for. — Cristian Javier Blanco García, Feb 02 '22 at 02:20
The keys that uniquely identify the rows in each table are catalogID, catalogItemID and companyComponentFlavorID. — Cristian Javier Blanco García, Feb 02 '22 at 02:21

score 1 · Accepted Answer · answered Jan 28 '22 at 21:39

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.

If the only condition is `WHEN NOT MATCHED THEN INSERT` why not just use `INSERT... WHERE NOT EXISTS...` — Charlieface, Jan 30 '22 at 00:58
You could! Feel free to write that up as a completely valid alternative. — Ben Thul, Jan 30 '22 at 03:33
I tried in the way you indicated using MERGE and worked as intended, thanks a lot for the help. — Cristian Javier Blanco García, Feb 14 '22 at 14:57

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

1 Answers1