Sample Header Ad - 728x90

What is the best way to include a large amount of configuration data in a SQL project?

1 vote
1 answer
1984 views
I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure DevOps and deploys to places. I want to include/pack in (mostly static) configuration **data** for a few tables. Initially I used the script wizard to script the tables, then just put that script text in my post-deployment file (InitConfigs.PostDeployment1.sql). I've come across one config table, when scripted, is a nearly 600mb script. The size isn't the problem, it just seems like there must be a proper/better way to embed it in the project. Some things I've tried: * Export to flat file and embed somehow. - Seems messy and I have to figure out a way to import it in my post deployment script * Export single table bacpac - Doesn't seem to work because it tries to include entire database * Use Script wizard to generate a giant *.sql file. - I can add to the project and reference with SQLCMD :r. Seems odd. Is there some sort of data package dacpac/bacpac-style file that can wrap everything up nicely and can be easily imported? Perhaps compressed too?
Asked by Alex K (175 rep)
Apr 20, 2020, 11:40 PM
Last activity: Apr 21, 2020, 03:50 PM