Published 31 July 2019
Although SQL Change Automation has native support for synchronizing table data , which in theory could be used to seed a transactional table with data, this only suits smaller data sets (that is fewer than 10,000 rows). This is because the data synchronization functionality in SQL Change Automation uses INSERT statements with a literal VALUES clause, which isn't always the fastest way to populate a table in SQL Server with larger amounts of data.
If you're looking to insert a vast amount of data into a table, one of the most efficient ways is to save the data to a flat file and use the BULK INSERT T-SQL statement to upload the data. However, including a BULK INSERT as part of a systematic process is not without its difficulties, given that the SQL Server instance must be able to access the file via a local path at deployment time.
To make automating this process easier, SQL Change Automation allows you to package seed files along with your T-SQL scripts and includes a system SQLCMD variable called DeployPath that allows you to fully qualify the path to a flat file.
Within your IDE, the value of the $(DeployPath) variable will resolve to the root folder of your SQL Change Automation project.
However when you package the project and subsequently deploy the database (for example, using a release management tool such as Octopus Deploy or Team Foundation Server), note that the $(DeployPath) variable will instead resolve to the folder where the package was extracted to.
For an example, see using seed data.