Static Data Management in VS:DBPro
This document describes a potential solution to managing static data in
Visual Studio Team Edition for Database Professionals.
What is static data?
A possible solution
Data script generator tool
What is static data?
For the purposes of this document database data will be split into three
- User Data: Data entered by users of the system relating to their role in
the application; for example, a user's contact details
- Control Data: Data required by the system and is unlinked to user data,
but is volatile in the sense that it can be modified by users of the system
through the calling application; for example a welcome message on the front
page of a website that a manager may wish to change based on the day of the
- Static Data: Data that is required in order for the system to function,
but is not changed through the calling application; for example a list of
employee statuses (Permanent, Contractor, Employment Ended). Usually used as
'database enums' with other tables having foreign keys pointing to them
Studio Team Edition for Database Professionals is a superb program that
manages the schema of your database visually and is capable of storing all
content and changes in source control. Unfortunately it does not have a system
to deal with static data, which makes it difficult to run unit tests and
automated test plans based on a blank database created from the schema.
Currently there are two ways round this; one is to use an insert script to
populate the required tables with the correct data and reference the script in
the post deployment script, and the other is to use a data generation plan with
a weird setup to attempt to create the data with the correct IDs. Neither
situation is fully workable though, as the former will cause problems when you
attempt to run updates to a live database (as the data will already exist), and
the latter will produce inconsistent data. In addition, neither solution allows
us to manage our static data through source control. We could also store our
static data in a separate database, however, we would lose change control over
the data as it is stored out of the database project and in binary form.
A solution requires the following characteristics:
- We need to manage maintenance of the data, not just getting it into the
database in the first place, e.g. I want to change the name of one of the
entries in a control data table and have that change automatically processed
as part of my next release to the live database along with any schema
- The data has to be held in text format to allow source control. We can't
use a database to hold our data as this would not be practical for source
- The data needs to be synchronisable, but only one-way, for example we
can run our changes against any database that is true to our schema and the
data will not be duplicated in the table, but any rogue entries entered
directly into the database will be passed back to the database project
- All this needs to be done as part of the normal release process inside
A possible solution
A nice solution would therefore be to have a script for each static data
table which defines a table variable with the same signature as the target
database, populates the variable using standard insert statements, and then the
data in the variable can be used to update the target static data table. An
example is shown below:
*** Static data management script ***
-- This script will manage the static data from
-- your Team Database project for tblEmployeeType.
PRINT 'Updating static data table tblEmployeeType'
-- Set to your region's date format to ensure dates are
SET DATEFORMAT dmy
-- 1: Define table variable
DECLARE @tblTempTable TABLE (
-- 2: Populate the table variable with data
-- This is where you manage your data in source control. You
-- can add and modify entries, but because of potential foreign
-- key contraint violations this script will not delete any
-- removed entries. If you remove an entry then it will no longer
-- be added to new databases based on your schema, but the entry
-- will not be deleted from databases in which the value already exists.
INSERT INTO @tblTempTable (EmployeeTypeId,
Description) VALUES ('1', 'Permanent')
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('2',
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('3', 'Employment
-- 3: Insert any new items into the table from the table
SET IDENTITY_INSERT tblEmployeeType ON
INSERT INTO tblEmployeeType (EmployeeTypeId, Description)
SELECT EmployeeTypeId, Description
FROM @tblTempTable WHERE EmployeeTypeId NOT IN (SELECT EmployeeTypeId FROM
SET IDENTITY_INSERT tblEmployeeType OFF
-- 4: Update any modified values with the values from the
UPDATE LiveTable SET
LiveTable.Description = tmp.Description
FROM tblEmployeeType LiveTable
INNER JOIN @tblTempTable tmp ON LiveTable.EmployeeTypeId = tmp.EmployeeTypeId
PRINT 'Finished updating static data table tblEmployeeType'
The above script will insert and update data in the target table based on the
data entered in the second section. This allows the DBA to add and modify
values, and the changes will automatically be propagated to the database on
execution. The script can also be held inside the database project, and
therefore also in source control, if required. The scripts should be held in a
separate folder under the 'Scripts' folder in the database project called 'StaticData',
and then the post-deployment script can call the individual update scripts like
Update: Thank you to Carlos Vacca for pointing out an error when scripting a
table with a composite primary key. This has been fixed in version 1.3.
Version 1.4 fixes a problem with null datetime columns being added as an empty
string, which caused them to be added as date minvalue.
Note: If you are using the new GDR edition of DBPro then you will need to
edit the DefaultTemplate.sql file in the installation directory (C:\Program
Files\Static Data Script Generator) and add a GO statement to the end.
Of course, creating scripts for all static data tables in an existing
database is a pain, as the table definition, the insert statements and the
update statement will all need to be changed to match the schema of the table.
Therefore you can download and use my
Static Data Script Generator tool for free
and use it to create your scripts for you.
To use the tool, install it using the link above, and navigate to the
installation folder (default C:\Program Files\Static Data Script Generator).
Change the connection string in the application configuration file (Static Data
Script Generator.exe.config) to point to your database, and then run the
application. The application will load all tables from your database into a
You can then select which tables you would like to create a script for. It is
recommended to populate the tables with your initial data before creating the
script (even just one record) in order to allow the script to create insert
The application will create a script for each table, and therefore you need
to provide an existing directory the application will drop the files into. Note
that if the file already exists it will be overwritten. If you would like the
application to create the post-deploy include statements for you to copy and
paste into your post-deployment script then click the 'Create index script'
checkbox. Click 'Generate Scripts' to create the files.
The application loads in an SQL template to drop the generated code into. You
can change the format of this file by editing the DefaultTemplate.sql file in
the application directory.
After the files have been generated, create a folder in your database project
under the 'Scripts' directory called 'StaticData' and import the scripts into
this folder. You can then copy and paste the contents of the index.txt file (if
you checked the 'Create index script' option) into your
Any problems / questions / suggestions?