static data, Bulk insert for get latest
Do not use transactions but use bulk insert in order to reduce the get latest time. A 'flush and refill' approach using bulk insert should be faster in hours with 10K plus records.
A two processor, dual core computer with 8GB of RAM and a Windows experience index of 6.5 takes over 8 hours to get lates on 50K records.
Thanks for this feedback and for the votes for this suggestion.
For users who need to manage larger volumes of static data, we currently recommend versioning the database with SQL Change Automation. SQL Change Automation has stronger support for static data:
- Supports column filtered static data tables in the SCA plugin in SSMS
- Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
- Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts
I do understand that you are looking for this feature in SQL Source Control, but wanted to surface this option for other readers who may be interested in either tool.