Generate seed data in Visual Studio 2010 Database project

Question

I have a database and a database project in Visual studio 2010. I inferred the schema in the database project successfully but I also need to import somehow the data in a few tables (Country, State, UserType etc.) that are reference tables and not really data tables.

Is there a way?

The only way I found so far is generating data scripts in SQL Server Management Studio and placing this script in the post-deployment script file in the database project.

Any simpler way?

Answer 1

this is pretty much how I've done it before.

I would just make sure that each statement in you script look something like:

IF (EXISTS(SELECT * FROM Country WHERE CountryId = 1))
    UPDATE MyTable SET Name = 'UK' WHERE CountryId = 1 AND Name != 'UK'
ELSE
    INSERT INTO MyTable (CountryId, Name) VALUES (1, 'UK')

This means that each time you deploy the database your core reference data will be inserted or updated, whichever is most appropriate, and you can modify these scripts to create new reference data for newer versions of the database.

You could use a T4 template to generate these scripts - I've done something similar in the past.