Custom Querying: REPLICATE Command

Version 24.3.9110


Custom Querying: REPLICATE Command

Version 24.3.9110


CData Sync manages all transformations in a declarative manner by using a special purpose SQL command: REPLICATE. The REPLICATE command enables you to define the data that is selected and the transformations that are applied as well as to map the data to a destination table.

Note: The Sync application contains a full SQL-92-compliant engine that dynamically translates standard SQL queries into the source API calls.

REPLICATE Syntax

REPLICATE { 
  DestinationTableName
  (ColumnDefinition [ , ... ] [TableConstraint])
  [WITH {OptionName=OptionValue|OptionName} , ... ]
  { SelectStatement | TableReference> } 
}

ColumnDefinition := ColumnName DataType
TableConstraint := PRIMARY KEY(ColumnName,...)
OptionName := DropTable | TruncateTable | AlterSchema ...
OptionValue := 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%'