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:

enter image description here

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

    public class SqlDatabaseSetup

        public static void InitializeAssembly(TestContext ctx)
            // Setup the test database based on setting in the
            // configuration file


using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
  • 1
    The typical place for tear down scripts is in the Post-test, where you could include TRUNCATE or DELETE for the table(s) affected by the test. The general principals are that unit tests should not be dependent on one another and the outcome of a given unit test, pass or fail, should not affect the outcome of other tests so that they can be run independently of one another.
    – Dan Guzman
    Oct 24, 2018 at 10:09
  • Agree with @DanGuzman, Still in case of relational data with Cascade Delete in Procedure we need to check the effect in relational table with cascade effect. This will require Tests created to each table separately and need to run each test in related sequence.
    – MarmiK
    Nov 16, 2018 at 16:12

1 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:


Direct link to the raw script:


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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.