Generating Single Scripts
Generating post deploy scripts involve a three step process. First any records in the source tables that are marked as deleted (IsDeleted = 1) are removed. This ensures that higher level environments are not filled with records that aren’t intended for use.
Second, the merge statements are generated using the adm.pGenerateMergeStatement stored procedure. The stored procedure will generate a single XML text record containing the entire merge statement for the table. This stored procedure has two required paremters:
- @table_name – the name of the table. For example ‘tTradingPartner’
- @schema – the schema name. For example ‘tp’
Below is example output of the stored procedure.
Generating Multiple Related Scripts
To make this process easier, the adm.pGeneratePostDeployStatements stored procedure can be used to generate merge statements, similar to above, for related groups of tables. For example, it can be used to generate scripts that can be used to migrate all routes and custom steps from one environment to another.
EXEC [TctepDb].[adm].[pGeneratePostDeployStatements]
@GenPartners = 0,
@GenSecurity = 0,
@GenRoutes = 1,
@GenRules = 0,
@GenTransactionSets = 0,
@GenReports = 0,
@GenServices = 0
GO
