MssqlMerge Pro supports its own scripting language that can be used from the command line and has a lot of features provided by GUI. Here is the script example which copies all data from AdventureWorks DB to the blank database with the same structure:
LogTo( fileName: 'c:\temp\myLog.txt', ifExists: Append, addTimestamp: true, bufferSize: 100); Set $db1 = OpenDatabase(connectionString: 'Server=.\myServer;Database=AdventureWorks2016;Trusted_Connection=True;'); Set $db2 = OpenDatabase(connectionString: 'Server=.\myServer;Database=AdventureWorks2016Blank;Trusted_Connection=True;'); /* DependencyLevel sorting: process tables without foreign keys first, than tables dependant on processed tables and so on:*/ ForEach $table in GetCommonTables( nameRegex: '', commonPrimaryKey: true, sortBy: DependencyLevel, sortOrder: Asc, sortByDependencyLevelDb: $db1) MergeData( changeType: New, fromDb: $db1, toDb: $db2, fromTable: $table, identityInsert: True, withTransaction: True, onError: StopScript);
Save such kind of text to text file with .ksdms extension and use the following command to run this script:
"c:\Program Files (x86)\MssqlMerge\MssqlMergeCmd.exe" myscript.ksdms
To verify script syntax without execution, you can use /checkOnly switch:
"c:\Program Files (x86)\MssqlMerge\MssqlMergeCmd.exe" myscript.ksdms /checkOnly
It will parse the script, validate its syntax and report to console output if it has any errors.
Here are the most common rules of this scripting language:
Setup logging settings
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
fileName | String | No | Log file name | |
ifExists | Custom | Yes | Append |
Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new log entries at the end of existing file |
addTimestamp | Boolean | Yes | True | Specify timestamp of log entries |
logDataResult | Boolean | Yes | True | Add an entry on each processed record like 'Merging [DatabaseLogID] = 1.. Done.' |
logDataScript | Boolean | Yes | False | Add entry of each executed INSERT/UPDATE/DELETE statement |
bufferSize | Number | Yes | 0 | Write log entries in batch mode, can improve performance, but some log entries can be lost in case of abnormal program termination |
Add text to the log
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
text | String | No | Text of log entry |
Specify database connection and return its reference as a variable. Connection string specified as for .NET SqlClient, see https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
connectionString | String | No | Connection string text |
Returns a list of table names that exists in both databases. This list can be used only inside the ForEach statement, see example above.
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
nameRegex | String | Yes | Regex filter by table name | |
sortBy | Custom | Yes | Name |
Specify by which table attribute we need to sort result: Name - by table name DelendencyLevel - by table dependency level. See Table DL for more information. |
sortByDependencyLevelDb | Database variable | See description | Each database has its own foreign keys and dependency level of the same tables can be different. If we do sorting by DependencyLevel - this parameter becomes mandatory. Otherwise it is optional | |
sortOrder | Custom | Yes | Asc |
Specify sort order: Asc - ascending Desc - descending |
commonPrimaryKey | Boolean | Yes | If True - return only tables which have the same primary key definition in both databases, if False - only different. If not specified - return all tables |
Merge data of the given table from one database to another
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
changeType | Custom | Yes |
Specify which records needs to be merged: New - only records existing in source and missing in target Changed - records that exist in both DBs but have different values NewAndChanged - union of New and Changed records |
|
fromDb | Database variable | Yes | Source database | |
fromTable | String or variable | Yes | Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call | |
toDb | Database variable | Yes | Target database | |
identityInsert | Boolean | Yes | False | If target table ID column is identity/autoincrement: True - import ID values from the source database, False - ignore source ID values, generate new ID values for imported records. Note that in this case processed records will keep to be considered as New by application because they will have different IDs |
onError | Custom | Yes | Proceed | Error processing logic: Proceed - skip failed record and proceed to process the rest StopCommand - stop current MergeData execution and don't process rest of records StopScript - stop the whole script execution |
withTransaction | Boolean | Yes | False | Process all records as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance |
Delete data from the given table of the given database. You can specify second database to compare and delete only new/changed/both records
Parameter | Type | Optional | Default value | Description |
---|---|---|---|---|
changeType | Custom | No | All |
Specify which records needs to be deleted: All - all table records New - only records that are new comparing to another database Changed - records that exist in both DBs but have different values NewAndChanged - union of New and Changed records |
fromDb | Database variable | Yes | Database to be processed | |
fromTable | String or variable | Yes | Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call | |
comparedToDb | Database variable | No | Database to compare and identify New/Changed/NewAndChanged records | |
onError | Custom | Yes | Proceed | Error processing logic: Proceed - skip failed record and proceed to process the rest StopCommand - stop current DeleteData execution and don't process rest of records StopScript - stop the whole script execution |
withTransaction | Boolean | Yes | False | Process all records as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance |
Last updated: 2023-01-09