Steven edited this page Sep 6, 2015 · 4 revisions

Welcome to the DataMigrationScriptGeneration Tool wiki!

#Intro As you know SQL Server Data Tools (SSDT) is a modern database development tool for SQL Server and Azure SQL Database. As a database developer, you can design, develop, build, test and publish a database with the same ease as you would develop an application in Visual Studio.

Pre-deployment and post-deployment scripts execute Transact-SQL statements before and after the main deployment script, which is generated from the database project. A project can have only one pre-deployment and one post-deployment script. These scripts can be used for many purposes. For example: A pre-deployment script can copy data from a table that is being changed into a temporary table before re-formatting and applying the data to the changed table in a post-deployment script, You can insert reference data that must exist in a table in a post-deployment script. Before you insert the data, you can test whether the table already contains data. If the table is not empty, you must clear the existing data or specify that you want to always re-create the database before you deploy it. You can add a statement such as the following to your post-deployment script:

When you use SSDT to deploy database changes you can include a pre/post deploy script which is run after the dacpac and database has been deployed. The process looks like:

  1. sqlpackage.exe compares dacpac to database
  2. sqlpackage.exe generates a deploy script
  3. sqlpackage.exe runs pre-deployment script
  4. sqlpackage.exe runs deploy script (generated in step 2)
  5. sqlpackage.exe runs post deploy script.

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the following operations:

##The MERGE syntax consists of five primary clauses:

  • The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
  • The USING clause specifies the data source being joined with the target.
  • The ON clause specifies the join conditions that determine where the target and source match.
  • The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
  • The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

##How to generate The MERGE scripts from existing data from SQL Table? The DataMigrationScriptGeneration Tool will help to generate the MERGE scripts for your SSDT package based on existing data in SQL Tables. The merge scripts will be executed as the post scripts when SSDT package is publishing to target database.

##How to use the DataMigrationScriptGeneration Tool? The main window of the tool is below. Main Window

  1. Specify the SQL connection string.
  2. Choose the migration script option.
  3. Specify the output folder which the scripts will be saved.
  4. Select the tables which migration data will be retrieved and added to the migration scripts.
  5. Click generate button.
  6. Verify the generated scripts in output folders.
  7. The output file format is Merge_Data_[TableName]_Table.sql.

##How to include the generated scripts to SSDT as post-deployment scripts. When the migration scripts were generated into the output folders as a sample below. Generated Scripts

Now we open the SSDT project which you wish to include all above scripts as the post-deployment scripts. Then add all that scripts to a folder in SSDT project. Example I will add into the Scripts/ScriptData folder in my project as below. All Merge_Data_[TableName]_Table.sql scripts will be mark as Copy always to the Output Directory.

Static Data Merge Scripts

The Mergers_Data.sql will be set as the **PostDeploy **script of the project.

When publish the SSDT project to target database the postscripts will de execute to insert, update and delete static data in target database. The post-deploymet script.

Build your project and publish it into target Database to see the result. Hope this tool help.

#Download

#Contribution If you found any issue during using this tool please report it into Issues Management here. Your idea is very important to help us to make the tool bester and more useful.

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.