Including Data in a SQL Server Database Project

September 16, 2013

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

toon vanhoutte

Blogging about connecting devices, systems and people! On-premises and in the cloud. Microsoft stack only.

Ken Cenerelli

My life in software development

scomfaq.wordpress.com/

Things about System Center and Cloud...

Build HoloLens

All about HoloLens

DevDays®

For web designers and cloud developers

Build Azure

All about the Microsoft Cloud

vishal patel

Software Developer, Windows 8 and Windows Phone Enthusiast

SQL with Manoj

SQL Server (TSQL) Programming, DB concepts, Tips & Tricks with >400 articles... comments welcome!!!

dragablz.wordpress.com/

Dragable, tearable, dockable WPF TabControl. Open source to boot.

Adrian Hall

Because Developers are Awesome

Ratish Philip's Blog

Another adventure in code...

codematrix

Application Architecture at it's best

One Unicorn

Thoughts from one member of the Entity Framework team...

Rachel Lim's Blog

Simplifying programming into something I can understand

.NET Developer's Blog

Software Development, Flying and Life

The Flying Maverick

Software Development, Flying and Life

Hiking Photography

Beautiful photos of hiking and other outdoor adventures.

paint.net blog

The best free image and photo editor. By Rick Brewster.

Learning by Shipping

products, development, management...

%d bloggers like this: