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%'