Add Reference Data to Tables When You Deploy the Database

  • Article

You can insert reference data into one or more tables in your database by adding statements to the post-deployment script of your database project. Before you insert data into a table, you can test whether it already contains data. If the table is not empty, you must clear the existing data or specify that you want to always re-create the database before you deploy it. You can add a statement such as the following to your post-deployment script:

IF (EXISTS(SELECT * FROM [dbo].[MyReferenceTable]))
BEGIN
    DELETE FROM [dbo].[MyReferenceTable]
END

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Common Tasks

Supporting Content

Create or update a post-deployment script: You can modify the post-deployment script for your database project to insert data into one or more tables. Instead of modifying the post-deployment script to add data, you can create a separate script and then include it as part of the post-deployment script.

Build and deploy the database project: You should build and deploy your database project into your isolated development environment so that you can test your changes.

Test your changes before you check in: You can run existing unit tests against the modified database or server to verify that your changes do not break any behavior of your application. You can also create unit tests to verify a new or changed behavior. You might need to change your data generation plan to support new or changed unit tests. You might also want to update your isolated development database with data from a test, staging, or production server.

Check in your changes: After you have tested your changes to the post-deployment scripts, you can check your changes in to version control to share them with your team. When you check in your changes, you should also resolve any related work items.

Troubleshoot problems: You can learn more about how to troubleshoot common problems with database unit testing, database projects, build creation, and deployment.