Support Center

Including Static Data in DB Deployments [Legacy Method]

Last Updated: May 01, 2014 09:23PM AEST

This article has moved to a new home. For up-to-date documentation please continue reading at http://doc.ready-roll.com/display/RRSQLDOC/Static+Data


By the end of this tutorial, you will be able to:

  • Generate MERGE statements from data contained in a live SQL Server table
  • Deploy static/lookup data to target database servers from your ReadyRoll DB project
 
This article applies to:
  • SQL Server 2008, 2008 R2 and 2012
  • Visual Studio 2010 and 2012 (with the ReadyRoll extension installed)

 
 

Introduction

 
A key part of forming a database development process is coming up with a way to manage the deployment of static data (sometimes referred to as lookup data). This data can include things that are needed by the calling application to work such as street types, zip/post codes and product categories.
 
This tutorial uses the "legacy" method of including static data in database deployments: as of ReadyRoll 1.6, data synchronization is supported by the ReadyRoll DBSync tool in Visual Studio. Read more
 
In this tutorial we'll make use of the "sp_generate_merge" stored procedure utility to add a Post-Deployment script to your ReadyRoll project. These scripts will perform a single MERGE statement for each of your static data tables by including all records within a multi-row VALUES clause.
 
 
 

1. Install the "sp_generate_merge" proc

 
Download the "master.dbo.sp_generate_merge" stored procedure and install it by simply running the script on your dev SQL Server instance. This will install the utility as a system procedure within the [master] database so that it can be used within all of your user databases.
 
Note that you do not need to add this script to your ReadyRoll database project, nor do you need to deploy the stored procedure to any server other than your Development environment.
 
Full details of the "sp_generate_merge" stored procedure can be found on GitHub.



 

2. Generate a MERGE statement from existing data

 
In this step we'll generate a single MERGE statement containing all the records from the "Region" table in the Northwind database:

 
Firstly, open SQL Management Studio and connect to your development database server and ensure that your SQL client is configured to send results to grid, rather than text.
 
Execute the stored procedure, providing the source table name as a parameter. If your table is in a non-default schema, be sure to supply the @schema parameter with its name.
 
For example:
EXEC Northwind.dbo.sp_generate_merge 'Region', @schema='dbo'


 
Click the hyperlink in the result set to open up the Xml fragment in a new document window.




Copy the document contents (excluding the Xml tags) to the clipboard and switch back to Visual Studio.

In the next step we'll create a script to be executed during each deployment which will ensure that the table is always in-sync.


 
 

3. Add a deployment script to your project

 
Within Visual Studio, open the Solution Explorer tool-window (View... Solution Explorer) and expand your ReadyRoll database project's "Scripts\Post-Deployment" node.


 
Right-click the Post-Deployment node and select "Add... Script". In the New Script dialog, select the "Script (Not In Build)" template and specify a name that includes the next sequential number, eg. "01_Populate_Region_Data.sql". 

NOTE: Naming your Pre/Post-Deployment scripts in this way isn't mandatory for your project to build, however we recommend sequentially numbering your scripts to make it easier to explicitly define the order of execution. This is particularly important if there are interdependencies between your static data tables.
 
Paste the SQL copied in the previous step into the newly created script file.

 
With the benefit of syntax highlighting, we can now take a bit more of a closer look at what is going on in the generated script:
 

 
To test your new script, deploy your database project to your development SQL Server (Build... Deploy [Project Name]). The Output window should display results similar to below:


 
 

4. Making Changes to Static Data


To see how the script can be re-used to repeatedly deploy changes in your static data, try making changes to the data in the script and re-run the deployment. The rows affected by the MERGE should reflect your additions and modifications to the file.
 
If you'd prefer to initiate changes to data by editing the table data directly in SSMS, you can do so by simply re-running the "sp_generate_merge" proc after making changes to the live data. Copy+Paste the generated code as you did in the previous steps to update your Post-Deployment script with the new changes.



 

Summary

An important consideration when deciding to include static data as part of your deployment is whether the data might be modified by your Production system users, outside of source control. This is particularly important when performing a release of your database, because any changes made by users outside of your deployment process will be replaced upon your deployment script's execution.
 
However, by including the data in your project, you gain all the benefits of source control plus the benefits of having all your developers work with a consistent data as part of their testing.

What makes the MERGE statement so useful is not only its ability to insert, update or delete data in one succinct, atomic operation but also the fact that the statements are re-runnable. This allow for the source script to be edited and re-used for deployment to all target environments.


 
readyroll.support@red-gate.com
http://assets3.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete