> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sync.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom Querying: REPLICATE Command

export const siteNameShort = "Sync";

export const siteName = "CData Sync";

{siteName} 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 {siteNameShort} application contains a full SQL-92-compliant engine that dynamically translates standard SQL queries into the source API calls.</Note>

**REPLICATE Syntax:**

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

ColumnDefinition := ColumnName DataType
TableConstraint := PRIMARY KEY(ColumnName,...)
OptionName := DropTable | TruncateTable | AlterSchema ...
OptionValue := Literal | Identifier
```

<Note>The REPLICATE command supports additional options through the WITH clause that control how data is processed during replication. Some options affect execution behavior rather than transformations. For example, you can enable parallel partitioned reads to improve snapshot performance for large datasets. For more information, see [Snapshot Performance with Parallel Partitioned Reads](../../getting-started/configuring-jobs#snapshot-performance-with-parallel-partitioned-reads).</Note>

## 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:

```sql theme={null}
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).

```sql theme={null}
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:

```sql theme={null}
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 {siteNameShort} 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:

```sql theme={null}
REPLICATE [Table] (KeyColumnName int)
```

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

```sql theme={null}
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:

```sql theme={null}
REPLICATE Airlines (Id int PRIMARY KEY)
```

## Using Variables in REPLICATE Queries

You can reference dynamic values in REPLICATE queries by using variable syntax. {siteNameShort} supports two types of variables: environment variables and pipeline variables.

* **Environment variables:** You set these variables in a pre-job event script. Use the syntax `{env:variablename}`, as shown in this example:

  ```sql theme={null}
  REPLICATE [Orders] SELECT [ID], '{env:currenttimestamp}' AS [DateUpdated] FROM [Orders]
  ```

  For more information, see [Creating Environment Variables](../../events/events#creating-environment-variables) and [Using Environment Variables in the REPLICATE Query](../../events/events#using-environment-variables-in-the-replicate-query).

* **Pipeline variables:** These variables are available when a job runs as part of a pipeline. Use the syntax `{pipeline:variablename}`, as shown in this example:

  ```sql theme={null}
  REPLICATE [Orders] SELECT * FROM [Orders] WHERE [OrderDate] > '{pipeline:cutoffdate}'
  ```

  For more information, see [Using Pipeline Variables](../../getting-started/administration/pipelines-page#using-pipeline-variables).