When capturing database changes in version control to use in automated deployments, it's important to also capture transactional or static data changes as well as schema object changes.  Transactional data could be specifying a default when adding a new column or updating transactional data already in a Production database, e.g., increasing salaries by x% or splitting a column into two new columns.

In addition, it is important to version control and deploy changes to static data as well.  Static data is also known as configuration or lookup data.  It's usually small sets of data that need to be deployed alongside the application changes for the application to run correctly.  This can include data such as zip/postal codes, country lists, or even application settings.

Capturing changes as part of a versioned migration script

When you Generate migrations using Redgate Change Control, you select which changes in your development database you want to create scripts for, i.e., which changes are ready to be committed to version control.  After selecting which changes, there is a review step.  Any table structure changes are shown in a versioned migration script at the top of this page.  In addition to specifying the exact version and filename you want, you can also edit this script to include any data changes as well.  Since this script was generated from changes already on your development database, it will not be re-run against the development database.  Most users will write and execute the data change script against the development database in the IDE of their choice and then just copy and paste it into the script at this point.  This script can then be committed/pushed to version control and the whole script will be run in CI/CD environments (learn more about Deploying database changes).

Adding a new versioned migration script to the project folder

If you need to add a versioned migration script that doesn't correspond with any table schema changes, then simply navigate to the project folder using the link in Redgate Change Control and create or save your change script in this directory using the correct filename.  Make sure the filename starts with a capital V, the number you want it executed in, two underscores, and then a description, e.g., V003_20201022__UpdatingJobTitles.sql.

Using Redgate's Data Compare technology to generate changes 

You can use our Data Compare technology as part of Redgate Deploy to generate a deployment script for data changes in static/lookup tables.  You can use these tools to synch to a scripts folder and version control how the static data changes over time.  You can also compare your development database to a scripts folder containing the latest version from version control or to a downstream database like n Production to generate a deployment script that captures any changes to your static data.  Once you generate the deployment script, simply save it into your project folder using the naming convention described above.  The versioned migration script can then be committed and pushed to version control and flow through your automated deployment process to your downstream environments (e.g., Test, QA, Stage, Production).

Learn more about how to use Data Compare for Oracle or SQL Data Compare, which are part of Redgate Deploy.  Both of these tools have a GUI, command line, and Docker containers.