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 Tasks

This section provides examples for several common REPLICATE tasks.

Maintain a Copy of a Table in Your Destination

If you want to maintain a copy of a table in your destination, you can use the following REPLICATE command:

REPLICATE Table

This 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 One Table from Another Table

You can use the following command to replicate one table from another table (in this case, replicating REP_Table from Table).

REPLICATE REP_Table SELECT * FROM Table

Select Columns and Perform Operations on Data before Replication

To select specific columns and perform operations on data before it is replicated, you can use the following REPLICATE command:

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

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.

Replicating Tables in a Destination with and without a Primary Key

You can customize how Sync creates tables in your destination by redefining columns in the REPLICATE statement. This approach allows you to control whether to include or omit primary keys when a table is created in a destination.

To create a destination table without a primary key, even when one exists in the source, you can explicitly define the column without the PRIMARY KEY keyword in the column definition, as shown in this syntax:

REPLICATE [Table] (KeyColumnName int)

For example, to create a table named Airlines without the Id column as a primary key, submit this command:

REPLICATE Airlines (Id int)

To define a primary key in the destination, even when one does not exist in the source, use the same syntax but include the PRIMARY KEY keyword in the column definition, as shown here:

REPLICATE Airlines (Id int PRIMARY KEY)