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;
Best Answer
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:
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/