Replicate Command


Replicate Command


CData Sync manages all transformations in a declerative fashion using a special purpose SQL command: REPLICATE. The REPLICATE command lets you define the data that is selected, the transformations that are applied, and map the data to a destination table.

REPLICATE Syntax

REPLICATE { 
  <destination_table_name>
  (<column_definition> [ , ... ] [<table_constraint>])
  [WITH {<option_name>=<option_value>|<option_name>} , ... ]
  { <select_statement> | <table_reference> } 
}

<column_definition> := <column_name> <data_type>
<table_constraint> := PRIMARY KEY(<column_name>,...)
<option_name> := DropTable | TruncateTable | AlterSchema ...
<option_value> := <literal> | <identifier>

Common Replicate Queries

Use the following statement to maintain a copy of a table in your destination. The Replicate command creates a Table in the destination database if it does not already exist. If applicable, the REPLICATE statement retrieves recent changes (newly updated and inserted records in the source) and applies them to the destination.

REPLICATE Table

Use the following statement to replicate Table to REP_Table.

REPLICATE REP_Table SELECT * FROM Table

Use the following statement to select specific columns and perform operations on data before it is replicated. This command creates the table REP_Table with the columns DateModified and FullName. The FullName column is a concatenation of FirstName and LastName from the Table table.

REPLICATE REP_Table SELECT DateModified, CONCAT(FirstName,' ',LastName) AS FullName FROM Table WHERE FirstName LIKE '%Tim%'

CHECKCACHE Command

The CheckCache command can be used to repair the destination copy of the data after a Replicate command has been run.

CHECKCACHE Syntax

CHECKCACHE <cached_table_name>  
  [ AGAINST { <tablename> | ( <select_statement> )}]
  [ WITH REPAIR ] 
  [ SKIP { MODIFIED | DELETED }]
  [ START {<datetime> | <date time function>}] 
  [ END {<datetime> | <date time function>}]

Common CheckCache Queries

Use the following statement to validate and repair the destination table. This command will query both the source and destination tables, insert any missing records in the destination, update any out-dated records in the destination, and remove any records missing in the source.

CHECKCACHE REP_Table AGAINST Table WITH REPAIR;

Use the following statement to only repair the cache within the specified date range.

CHECKCACHE REP_Table AGAINST Table WITH REPAIR START '2018-01-01' END '2019-01-01'

Use the following statement to only repair certain columns in the cache within the specified date range.

CHECKCACHE REP_Table AGAINST (SELECT Id, Name, DateModified FROM Table) WITH REPAIR START '2018-01-01' END '2019-01-01'

Use the following statement to only repair the cache database by removing deleted records over the previous month.

CHECKCACHE REP_Table AGAINST Table WITH REPAIR SKIP MODIFIED START LAST_MONTH()