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. |
|
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. |
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.