How can I perform a MERGE in DbFit?

I'd like to ensure reference tables are populated before performing tests against a database. The specific data I want to use may or may not already be in the test database so I'd like to perform a MERGE (also known as an UPSERT), which would insert the data if it wasn't already in the table and update it if is was.

From what I can see from Googling, DbFit doesn't seem to support the MERGE command and also doesn't support executing SQL loaded from an external SQL script file (plan B was to create the MERGE in a SQL script file, then load the file and run it in DbFit).

Is there any straight-forward way of performing a MERGE in DbFit, or will I need to create a special fixture class to do it?

Solution

I figured out how to do it using the just the standard commands from FitSharp's version of DbFit.

This example is executing against a SQL Server database. Oracle has a similar syntax for its MERGE statement, although I don't know how it handles temporary tables. MySQL does not have a MERGE statement; it has a non-standard command that achieves the same thing. It also supports temporary tables but I'm not familiar with the syntax for MySql temporary tables.

Here's the definition of a target table I want to merge data into:

CREATE TABLE Student 
(
    [Name] NVARCHAR(200), 
    DateOfBirth DATETIME, 
    Notes NVARCHAR(1000)
);

Here's the DbFit Flow mode page that will merge data into it:

!| Execute | CREATE TABLE #MergeSource ([Name] NVARCHAR(200), DateOfBirth DATETIME, Notes NVARCHAR(1000)); |

!| Insert | tempdb.dbo.#MergeSource |
| Name       | DateOfBirth | Notes                |
| Jane Smith | 1997-09-24  | These are some notes |
| John Doe   | 2000-04-06  | Other notes          |

!| Execute | !-
MERGE INTO Student AS target
USING 
    (
        SELECT [Name], [DateOfBirth], [Notes]
        FROM #MergeSource
    ) AS source
ON target.[Name] = source.[Name]
WHEN MATCHED THEN
    UPDATE 
    SET [DateOfBirth] = source.[DateOfBirth], 
        [Notes] = source.[Notes]
WHEN NOT MATCHED BY TARGET THEN 
    INSERT ([Name], [DateOfBirth], [Notes])
    VALUES (source.[Name], source.[DateOfBirth], source.[Notes]);
    -! |

!| Query | SELECT [Name], DateOfBirth, Notes FROM Student; |
| Name       | DateOfBirth | Notes                |
| Jane Smith | 1997-09-24  | These are some notes |
| John Doe   | 2000-04-06  | Other notes          |

Firstly a temporary table is created to act as the data source for the MERGE. The data to merge is inserted into the temp table then the MERGE statement is executed. The Query command at the end is not needed for the MERGE, it was only added to check the target table has been updated correctly.

Note the Insert command must use a three-part name for the temp table, at least in SQL Server. When the Insert command is executed against SQL Server, behind the scenes it queries sys.columns to get column information about the table being inserted into:

exec sp_executesql N'select c.[name], TYPE_NAME(c.system_type_id) as [Type], c.max_length, 
    0 As is_output, 0 As is_cursor_ref, c.precision, c.scale
    from tempdb. sys.columns c 
    where c.object_id = OBJECT_ID(@objname) 
    order by column_id',
        N'@objname nvarchar(23)',
        @objname=N'tempdb.dbo.#MergeSource'

The OBJECT_ID function will only return the object ID of a temp table if the table name is specified as a three-part name. This is because SQL Server always creates temp tables in the tempdb database, not the database where the temp table is going to be used. The OBJECT_ID function will not find the meta data for the temp table unless it is told to look in the tempdb database for it.