Sql Server Data Tool (SSDT) projects allow us to define a database schema in a project file, wit the ability to easily develop database projects with the ability to build and generate a deployment script from the schema - meaning we don't have to worry about manually creating scripts to modify a database on different environments. Deploying an SSDT project will work out what changes need to be applied and run that against a target SQL instance.

Scripts can also be ran on Pre deployments and Post deployments, which works well for inserting static data into a database that will rarely / occasionally change or configuration and enum values such as customer titles

Generating Static Data

Generating a MERGE script from an existing table can be a pain in the neck, especially if we have a gazillion rows or you're really lazy. There's a really useful script floating about on the interwebs that can automate this process, preventing repetitive scripting of data (and also preventing typo's + user error!)

The script can be found here:

https://github.com/readyroll/generate-sql-merge/

The MERGE statement

For those unfamiliar with a MERGE statement, it will do the following:

• If the source row does not exist in the target table, an INSERT is performed
• If a given row in the target table does not exist in the source, a DELETE is performed
• If the source row already exists in the target table and has changed, an UPDATE is performed
• If the source row already exists in the target table but the data has not changed, no action is performed (by default)

Usage

Simply run the sp_generate_merge stored procedure against your master database then you can start using it on any database to script existing data:

USE AdventureTime

EXEC sp_generate_merge @table_name = 'Characters'

The above will return an XML file containing the full MERGE statement which will handle INSERT's, UPDATE's and DELETE's - along with any additional commands required to run it such as SET IDENTITY_INSERT OFF

Adding to an SSDT project

Simply add this to a new file in your SSDT project (in a new directory, such as ./StaticData/Characters.sql) with a build action of None

Reference this file from a StaticData file such as ./PostDeploy.Static.sql by adding the following:

:r .\StaticData\dbo.Characters.sql

Your IDE may complain as this is not valid SQL but do not worry - this is SQLCMD syntax! Don't forget to set the build action for this file to PostDeploy, otherwise this won't get compiled into the publish script when deploying a database

Summary

This script contains a range of features such as excluding columns although some data types are not (currently) supported such as images or custom types.

Filters can be added using the @from parameter