## Purpose
The compare and synchronize statement provides one-directional and bi-directional analysis and alignment of data in two tables. It is an extended version of SQL statements like 'upsert' and 'merge' found in other SQL implementations. The two tables can be located in any data container, enabling cross-data container synchronization of data. In most scenarios, data from both tables is downloaded and compared by Invantive UniversalSQL to establish the necessary actions. Some specific edge cases may have an optimized algorithm to reduce data downloads.
In an one-directional approach as specified by 'FROM' or 'TO', the compare and synchronize statement typically analyzes the differences between two tables with identically named columns, separating all differences in one out of four groups:
- insert: a row exists in the source table and not in the target table;
- delete: a row exists not in the source table, but exists in the target table;
- update: the row exists in both tables, but the column contents are different.
- none: the row exists in both tables and the column contents are equal.
Column values are matched on name. By default, all columns having identical names will be included in the matching process. The IGNORE CHANGES TO can be used to exclude columns by name from matching. IGNORE CHANGES TO is typically used to exclude changes in column values that should not trigger a DML-operation, such as for columns whose value is managed by the application, including technical keys.
Unique rows in both tables are matched using the list of columns as specified by 'IDENTIFIED BY'. Identifying column NULL contents are considered a specific value for matching; when both the source and target table have a null value, the rows will be compared. Rows are completely ignored for synchronisation when all identifying columns have a NULL value and the clause "IGNORE NULLS" is present as in "IGNORE NULLS", "IGNORE NULLS ON ALL" or "IGNORE NULLS ON IDENTIFIED BY".
Null column values are ignored when "IGNORE NULLS" is specified as "IGNORE NULLS", "IGNORE NULLS ON ALL" or "IGNORE NULLS ON VALUES". This allows columns to be filled more easy from multiple sources, each synchronize only completing for which it has data.
After analysis, the differences are be filtered to only contain a combination of DML-operations specified using 'WITH'. Applying all DML-operation types INSERT, UPDATE and DELETE would result in the two tables having identical contents. The INSERT and UPDATE operations by default apply to all columns, but columns can be excluded using 'ALL EXCEPT'. Excluding columns is typically used to leave out audit columns such as a generated creation date upon update and still have it included upon insert.
For better performance, SYNCHRONIZE uses bulk operations where supported, similar to CREATE TABLE and BULK INSERT. Bulk operations typically offer an order of magnitude better performance than solitary operations. However, error handling can be inconvenient since it is often unclear what specific row triggered an error. Neither, it is not easily established upon an error whether other rows in the same bulk operation have been processed. The BATCHSIZE clause allows specification of the number of rows per bulk operation with a minimum of 1. When BATCHSIZE is not specified, a platform-specific default value is used which can fluctuate due to dynamical management by Invantive UniversalSQL.
In case of bi-directional synchronization, the RESOLVE BY-clause enables specification of the
column names whose value determines what table contains the preferred row with most current values. Using left-to-right column preference, both values of all RESOLVE BY columns are inspected. The first column value to have a higher ranking value than the other table selects that table as source. The logic is reversed for a column from higher ranking to lower ranking when DESC is specified. The RESOLVE BY-clause is typically used in combination with columns containing a (synchronized) timestamp value such as UTC time.
The 'APPLY TO' syntax is reserved for future use to allow routing DML-operations to other tables.
By default, the SYNCHRONIZE statement will fail upon the first error returned. Using 'CONTINUE ON FIRST ... ERRORS' the threshold can be increased. However, the statement will fail whenever any error occurred during execution.
The differences can then either be:
- applied on the target table to make the tables equal, using bulk operations where supported.
- returned as a list of content differences (not yet available);
- returned as Invantive UniversalSQL DML-statements to achieve synchronization (not yet available).
After checking the output of the last two categories of analysis, the differences can be applied on the target table.
In a bi-directional approach, as specified by using the keyword 'AND' between the two table identifiers, deletes will not be returned from the analysis phase and replaced by inserts on the table which does not have the other table's row. The determination of the target for an update is based upon the resolution preference as defined by the 'RESOLVE' clause. The 'RESOLVE' clause either defines to always apply updates to the left or right table as listed in their order using, respectively, 'PREFER LEFT' and 'PREFER RIGHT'. Otherwise, the combined ordinal value of the columns listed in the 'RESOLVE BY' clause will be evaluated and the highest-ranking value is considered to be leading.
## Syntax
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
synchronizeStatement_start((START))
synchronizeStatement_start --> synchronizeStatement_0_0[synchronize]
synchronizeStatement_0_0 --> synchronizeStatement_0_1[COMPARE]:::quoted
synchronizeStatement_0_1 --> synchronizeStatement_0_2[tableSpec]
synchronizeStatement_0_2 --> synchronizeStatement_0_3[TO]:::quoted
synchronizeStatement_0_3 --> synchronizeStatement_0_4[AND]:::quoted
synchronizeStatement_0_4 --> synchronizeStatement_0_5[tableSpec]
synchronizeStatement_0_5 --> synchronizeStatement_0_6[synchronizeIudStatement]
synchronizeStatement_0_6 --> synchronizeStatement_0_7[identifiedByMultipleClause]
synchronizeStatement_0_7 --> synchronizeStatement_0_8[synchronizeIgnoreClause]
synchronizeStatement_0_8 --> synchronizeStatement_0_9[resolveByClause]
synchronizeStatement_0_9 --> synchronizeStatement_0_10[applyToClause]
synchronizeStatement_0_10 --> synchronizeStatement_0_11[limitClause]
synchronizeStatement_0_11 --> synchronizeStatement_0_12[RETURNING]:::quoted
synchronizeStatement_0_12 --> synchronizeStatement_0_13[SQL]:::quoted
synchronizeStatement_0_13 --> synchronizeStatement_0_14[DIFFERENCES]:::quoted
synchronizeStatement_0_14 --> synchronizeStatement_0_15[BATCHSIZE]:::quoted
synchronizeStatement_0_15 --> synchronizeStatement_0_16[numericConstant]
synchronizeStatement_0_16 --> synchronizeStatement_0_17[CONTINUE]:::quoted
synchronizeStatement_0_17 --> synchronizeStatement_0_18[ON]:::quoted
synchronizeStatement_0_18 --> synchronizeStatement_0_19[FIRST]:::quoted
synchronizeStatement_0_19 --> synchronizeStatement_0_20[numericConstant]
synchronizeStatement_0_20 --> synchronizeStatement_0_21[ERRORS]:::quoted
synchronizeStatement_0_21 --> synchronizeStatement_0_22[synchronizeTriggers]
synchronizeStatement_0_22 --> synchronizeStatement_end((END))
```
## synchronizeIudStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
synchronizeIudStatement_start((START))
synchronizeIudStatement_start --> synchronizeIudStatement_0_0[WITH]:::quoted
synchronizeIudStatement_0_0 --> synchronizeIudStatement_0_1[synchronizeInsertStatement]
synchronizeIudStatement_0_1 --> synchronizeIudStatement_0_2[OR]:::quoted
synchronizeIudStatement_0_2 --> synchronizeIudStatement_0_3[synchronizeUpdateStatement]
synchronizeIudStatement_0_3 --> synchronizeIudStatement_0_4[OR]:::quoted
synchronizeIudStatement_0_4 --> synchronizeIudStatement_0_5[synchronizeDeleteStatement]
synchronizeIudStatement_0_5 --> synchronizeIudStatement_0_6[synchronizeUpdateStatement]
synchronizeIudStatement_0_6 --> synchronizeIudStatement_0_7[OR]:::quoted
synchronizeIudStatement_0_7 --> synchronizeIudStatement_0_8[synchronizeDeleteStatement]
synchronizeIudStatement_0_8 --> synchronizeIudStatement_0_9[synchronizeDeleteStatement]
synchronizeIudStatement_0_9 --> synchronizeIudStatement_end((END))
```
## synchronizeInsertStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
synchronizeInsertStatement_start((START))
synchronizeInsertStatement_start --> synchronizeInsertStatement_0_0[INSERT]:::quoted
synchronizeInsertStatement_0_0 --> synchronizeInsertStatement_0_1[ALL]:::quoted
synchronizeInsertStatement_0_1 --> synchronizeInsertStatement_0_2[EXCEPT]:::quoted
synchronizeInsertStatement_0_2 --> synchronizeInsertStatement_0_3[columnList]
synchronizeInsertStatement_0_3 --> synchronizeInsertStatement_end((END))
```
## synchronizeUpdateStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
synchronizeUpdateStatement_start((START))
synchronizeUpdateStatement_start --> synchronizeUpdateStatement_0_0[UPDATE]:::quoted
synchronizeUpdateStatement_0_0 --> synchronizeUpdateStatement_0_1[ALL]:::quoted
synchronizeUpdateStatement_0_1 --> synchronizeUpdateStatement_0_2[EXCEPT]:::quoted
synchronizeUpdateStatement_0_2 --> synchronizeUpdateStatement_0_3[columnList]
synchronizeUpdateStatement_0_3 --> synchronizeUpdateStatement_0_4[IGNORE]:::quoted
synchronizeUpdateStatement_0_4 --> synchronizeUpdateStatement_0_5[CHANGES]:::quoted
synchronizeUpdateStatement_0_5 --> synchronizeUpdateStatement_0_6[TO]:::quoted
synchronizeUpdateStatement_0_6 --> synchronizeUpdateStatement_0_7[columnList]
synchronizeUpdateStatement_0_7 --> synchronizeUpdateStatement_end((END))
```
## synchronizeDeleteStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> synchronizeDeleteStatement
synchronizeDeleteStatement -->| | DELETE[DELETE]:::quoted
DELETE --> End((END))
```
## synchronizeUsingPSqlBlock
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
synchronizeUsingPSqlBlock_start((START))
synchronizeUsingPSqlBlock_start --> synchronizeUsingPSqlBlock_0_0[USING]:::quoted
synchronizeUsingPSqlBlock_0_0 --> synchronizeUsingPSqlBlock_0_1[PSQL]:::quoted
synchronizeUsingPSqlBlock_0_1 --> synchronizeUsingPSqlBlock_0_2[pSqlBlockNoBatchSeparator]
synchronizeUsingPSqlBlock_0_2 --> synchronizeUsingPSqlBlock_end((END))
```
## synchronizeTriggers
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
synchronizeTriggers_start((START))
synchronizeTriggers_start --> synchronizeTriggers_0_0[TRIGGERS]:::quoted
synchronizeTriggers_0_0 --> synchronizeTriggers_0_1[synchronizeTrigger]
synchronizeTriggers_0_1 --> synchronizeTriggers_end((END))
```
## synchronizeTrigger
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
synchronizeTrigger_start((START))
synchronizeTrigger_start --> synchronizeTrigger_0_0[BEFORE]:::quoted
synchronizeTrigger_0_0 --> synchronizeTrigger_0_1[INSTEAD]:::quoted
synchronizeTrigger_0_1 --> synchronizeTrigger_0_2[OF]:::quoted
synchronizeTrigger_0_2 --> synchronizeTrigger_0_3[AFTER]:::quoted
synchronizeTrigger_0_3 --> synchronizeTrigger_0_4[INSERT]:::quoted
synchronizeTrigger_0_4 --> synchronizeTrigger_0_5[UPDATE]:::quoted
synchronizeTrigger_0_5 --> synchronizeTrigger_0_6[DELETE]:::quoted
synchronizeTrigger_0_6 --> synchronizeTrigger_0_7[ON]:::quoted
synchronizeTrigger_0_7 --> synchronizeTrigger_0_8[LEFT]:::quoted
synchronizeTrigger_0_8 --> synchronizeTrigger_0_9[RIGHT]:::quoted
synchronizeTrigger_0_9 --> synchronizeTrigger_0_10[pSqlBlockNoBatchSeparator]
synchronizeTrigger_0_10 --> synchronizeTrigger_end((END))
```
## synchronize
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> synchronize
synchronize -->| | SYNCHRONIZE[SYNCHRONIZE]:::quoted
SYNCHRONIZE --> End((END))
synchronize -->| | SYNC
SYNC --> End((END))
```
## synchronizeIgnoreClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
synchronizeIgnoreClause_start((START))
synchronizeIgnoreClause_start --> synchronizeIgnoreClause_0_0[IGNORE]:::quoted
synchronizeIgnoreClause_0_0 --> synchronizeIgnoreClause_0_1[NULLS]:::quoted
synchronizeIgnoreClause_0_1 --> synchronizeIgnoreClause_end((END))
```
## resolveByClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
resolveByClause_start((START))
resolveByClause_start --> resolveByClause_0_0[RESOLVE]:::quoted
resolveByClause_0_0 --> resolveByClause_0_1[BY]:::quoted
resolveByClause_0_1 --> resolveByClause_0_2[sortedColumnList]
resolveByClause_0_2 --> resolveByClause_0_3[PREFER]:::quoted
resolveByClause_0_3 --> resolveByClause_0_4[LEFT]:::quoted
resolveByClause_0_4 --> resolveByClause_0_5[RIGHT]:::quoted
resolveByClause_0_5 --> resolveByClause_end((END))
```
## applyToClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
applyToClause_start((START))
applyToClause_start --> applyToClause_0_0[APPLY]:::quoted
applyToClause_0_0 --> applyToClause_0_1[TO]:::quoted
applyToClause_0_1 --> applyToClause_0_2[tableSpec]
applyToClause_0_2 --> applyToClause_0_3[","]:::quoted
applyToClause_0_3 --> applyToClause_0_4[tableSpec]
applyToClause_0_4 --> applyToClause_end((END))
```
## triggerRecordNameExpression
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
triggerRecordNameExpression_start((START))
triggerRecordNameExpression_start --> triggerRecordNameExpression_0_0[COLON]:::quoted
triggerRecordNameExpression_0_0 --> triggerRecordNameExpression_0_1[OLD]:::quoted
triggerRecordNameExpression_0_1 --> triggerRecordNameExpression_0_2[NEW]:::quoted
triggerRecordNameExpression_0_2 --> triggerRecordNameExpression_0_3[SOURCE]:::quoted
triggerRecordNameExpression_0_3 --> triggerRecordNameExpression_end((END))
```
## triggerRecordVariableExpression
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
triggerRecordVariableExpression_start((START))
triggerRecordVariableExpression_start --> triggerRecordVariableExpression_0_0[triggerRecordNameExpression]
triggerRecordVariableExpression_0_0 --> triggerRecordVariableExpression_0_1["."]:::quoted
triggerRecordVariableExpression_0_1 --> triggerRecordVariableExpression_0_2[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>]
triggerRecordVariableExpression_0_2 --> triggerRecordVariableExpression_end((END))
```