The other day I found myself looking at how I can include reference data in a new database as part of my SQL Server Database Project. Typically for my existing databases, I’ve just create post-deployment scripts to add/update/delete reference data as needed with each release. However with my new database, I have a number of tables and rows in each that I’ll need to deploy the first time.
The first option that comes to mind would be that I can do this as per usual and create a v1.0 script to populate the data, but I thought there should be a better way to maintain and deploy this data. What if it ever changes and I need it updated? I’ll then have to script specific post-deployment scripts. Not ideal.
I did a little searching and came across this old post on the SQL Server Data Tools Team Blog for Including Data in SQL Server Database Projects. This seems like a great option, but I already see there are a number of people complaining that this isn’t good enough and I would agree. I also have the added annoyance that I’m still stuck using SQL Server 2005, so the MERGE option isn’t even an option for me.
Further research shows another post on how to Mimic MERGE with the OUTPUT statement in SQL Server 2005. This looks like it will work for SQL Server 2005, but makes things more complex in my opinion. I really just want to declare in my database project the values the table should have on deployment and then let the build and deployment process determine what needs to be done.
I think for now I’ll stick with my first option and create an initial v1.0 post-deployment script. I’ll then take a look at what comes in Visual Studio 2013 later this year and if there are new options for addressing this issue.
We are moving to SQL Server 2012 by year end, so I can at least migrate to doing the MERGE approach at that time if there is nothing else available.
I hope this helps others in the same situation.
Enjoy!
The Flying Maverick 