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?
The problem
A possible solution
Data script generator tool

What is static data?

For the purposes of this document database data will be split into three categories:

  1. User Data: Data entered by users of the system relating to their role in the application; for example, a user's contact details
  2. 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 year
  3. 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

The problem

Visual 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:

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 updated correctly
SET DATEFORMAT dmy

-- 1: Define table variable
DECLARE @tblTempTable TABLE (
EmployeeTypeId int,
Description varchar(50)
)

-- 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', 'Contractor')
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('3', 'Employment Ended')


-- 3: Insert any new items into the table from the table variable
SET IDENTITY_INSERT tblEmployeeType ON
INSERT INTO tblEmployeeType (EmployeeTypeId, Description)
SELECT EmployeeTypeId, Description
FROM @tblTempTable WHERE EmployeeTypeId NOT IN (SELECT EmployeeTypeId FROM tblEmployeeType)
SET IDENTITY_INSERT tblEmployeeType OFF

-- 4: Update any modified values with the values from the table variable
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 so:

:r .\StaticData\tblEmployeeType.staticdata.sql

Data Script Generator Tool

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 listbox:

Image: Screenshot of the application

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 statements.

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 Script.PostDeployment.sql.

Any problems / questions / suggestions?

Home