KS DB Merge Tools logo KS DB Merge Tools
Documentation
MssqlMerge logo for SQL Server
aka MssqlMerge
KS DB Merge Tools for Oracle logo
KS DB Merge Tools for MySQL logo
KS DB Merge Tools for PostgreSQL logo
KS DB Merge Tools for SQLite logo
AccdbMerge logo
KS DB Merge Tools for Cross-DBMS logo

Scripting support

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:

  • Script consists of list of statements, each statement must be terminated with semicolon (';')
  • There are 3 types of statements:
    • Function call that does performs some action without providing result (LogTo(), LogPrint(), MergeData(), DeleteData())
    • Assignment to a variable result of function call (Set $db = OpenDatabase())
    • ForEach operator that iterates a variable thru result of function that returns a list (ForEach $table in GetCommonTables() /*Do some action - MergeData() or DeleteData()*/;), note that semicolon is required only after ForEach body action, not after GetCommonTables()
  • Each function has number of parameters, some parameters are mandatory and some optional, but parameter names are mandatory (LogPrint(text:'OK') is good, but LogPrint('OK') is the error)
  • Each function parameter has specified type, it can be:
    • String - strings are single-quoted
    • Boolean - True or False
    • Number - positive number
    • Custom value from specified list, applicable only for given parameter (like Append value for ifExists parameter of LogTo())
    • Database variable - result or OpenDatabase() function call
    • String variable - result or GetCommonTables() function call
  • Script supports block comments in C-style (/*here is some comment*/)

Return codes

  • 0: Success
  • 1: License validation error, run GUI mode to check
  • 2: Denied for Free
  • 3: Invalid command line arguments
  • 4: Script parsing errors
  • 5: Log initialization failure
  • 6: Script interrupted by onError: StopScript parameter
  • 7: Script interrupted by any other unknown error (DB connection failure, some unsupported database feature, etc)

LogTo

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

LogPring

Add text to the log

Parameter Type Optional Default value Description
text String No Text of log entry

OpenDatabase

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

GetCommonTables

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

MergeData

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

DeleteData

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