Add Reference Data to Tables When You Deploy the Database

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.	How to: Specify Pre-Deployment or Post-Deployment Scripts
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.	Build and Deploy Databases to an Isolated Development Environment
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.	Running Database Unit Tests Creating and Defining Database Unit Tests Generating Test Data for Databases by Using Data Generators Compare and Synchronize Data in One or More Tables with Data in a Reference Database
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.	Add Files to Version Control Using the Check In and Pending Changes Windows Check In Pending Changes Resolve, Close, or Reactivate a Work Item
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database unit testing, database projects, build creation, and deployment.	Troubleshooting Database Unit Testing Issues Troubleshooting Database Project, Build, and Deployment Issues

Related Scenarios

• Generating Test Data for Databases by Using Data Generators
  In addition to inserting reference data into one or more tables, you might want to insert other types of data into the other tables in your database. You can use a built-in or custom data generator to create realistic test data without exposing production data to your developers.

• Compare and Synchronize Data in One or More Tables with Data in a Reference Database
  You can also compare and synchronize the data that the tables in your databases contain. You might use this option if you have a large amount of reference data in an existing database, and you want to import it into your database.