7

I have been using a SQL Server project to manager the structure of a database.

First I created the project, then imported a database.

Then, when I need to make a schema change, say change a field name, I do it in the SQL Server Project, and then publish it to the actual database using the Schema Compare Option.

I would like to take this a step further to hold basic data that the database needs. Say for example a OrderType table with 2 records "PHONE", "WEB"

This data will be needed on all new instances of the database. Is there a way to keep these in the SQL Server project as well so that they don't get lost?

It seems the only way to do this now, is to keep an actual copy of the master database with the metadata in it, and then use a data-compare. But it would be great if the data could just get published at the same time as the schema so the resulting database is complete.

Ram
  • 3,092
  • 10
  • 40
  • 56
Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • As much as I would love to answer this question and get the bounty, there are already answers [here](http://stackoverflow.com/questions/5953180/creation-of-default-data-in-visual-studio-database-projects-sql-server), [here](http://stackoverflow.com/questions/1525591/do-you-put-your-database-static-data-into-source-control-how), [here](http://stackoverflow.com/questions/13843164/how-do-you-store-static-data-in-your-sql-server-database-project-in-vs-2012). Let me know if that helps. – Keith Nov 20 '15 at 18:18
  • @Keith Thx. I had looked at the first one before, but due to it's age, I thought there might be more recent data. However, I had not seen the second and third ones, and those had good data. If you want to post something as an answer, I will accept it. – Greg Gum Nov 20 '15 at 20:15
  • I went ahead and posted what I found on this subject. – Greg Gum Nov 21 '15 at 21:56

1 Answers1

4

There are two ways to preserve static data and publish it with a database.

  1. Have a "reference" database with static data populated. At the time of publishing a new instance, SQL Server Data tools has a "Data Compare" tool which allows you to compare to live databases, and creates a custom script to update one database with data from the other.

  2. Create scripts that contain insert statements, and then run these scripts at publish time. SQL Server Data tools has two tools to assist in this.

    a. Open the data table (right click on SQL Server object explorer, and select "View Data"), and then click on the "Script" button at the top. It will create an insert script for all rows in the table. More on Comparing Data from MSDN

    b. Then take this created script, and add it to the Database Project as a "Post Deployment" script. When you create a publish script for the database, any Post Deployment scripts in the project are automatically included in the master script. More on post deployment scripts from MSDN

Greg Gum
  • 33,478
  • 39
  • 162
  • 233