Reinitialize Table Values in SQL SSDT Unit Testing

Question

I am creating SQL Server Unit Tests. We are testing various stored procedures.

In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (truncate/delete) the database tables, and resetup for each test. This way every unit tests will have a clean environment to validate sprocs which insert, select, update, delete, etc,

Does anyone where or how to reinitialize the tables values in Sql Unit Testing? Resources are pretty new for unit testing in SQL SSDT VS 2017, so I think lot of people are trying to figure out and understand.

Feel free to show or add pictures below.

http://www.sqlservercentral.com/articles/Unit+Testing/155651/ http://www.erikhudzik.com/2017/08/23/writing-sql-server-unit-tests-using-visual-studio-nunit-and-sqltest/

Pictures in Visual Studio SSDT:

Also, trying to review this class in SQLDatabaseSetup.cs:

 [TestClass()]
    public class SqlDatabaseSetup
    {

        [AssemblyInitialize()]
        public static void InitializeAssembly(TestContext ctx)
        {
            // Setup the test database based on setting in the
            // configuration file
            SqlDatabaseTestClass.TestService.DeployDatabaseProject();
            SqlDatabaseTestClass.TestService.GenerateData();
        }

    }
}


using Microsoft.Data.Tools.Schema.Sql.UnitTesting;

Answer 1

I have created the usp_Generate_Merge_For_Table procedure a few years ago exactly for this purpose. It can generate a MERGE command with a Values Constructor in order to "re-initialize" the contents of a specific table.

You can find it here:

https://gist.github.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb#file-generate_merge_for_table-sql

Direct link to the raw script:

https://gist.githubusercontent.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb/raw/608177e77da0a42d686098ab10703bbba8c54d64/Generate_Merge_For_Table.sql

Simply create the procedure in your database, run it while sending it your table name as a parameter, like so:

EXEC usp_Generate_Merge_For_Table 'cities', 'dbo'

And then you can copy and paste its output in your Unit Testing project.

You can find more info and detailed instructions on how to use it here:

https://eitanblumin.com/2018/10/28/generate-merge-statement-as-a-snapshot-of-your-tables-content/