Replicating Data using SQL

To implement replication, SAP recommends that you define source files in your project to describe the replication settings, and then deploy these files to generate the run-time objects in the database. We will cover this approach later.

For now, we will implement replication by executing SQL statements in the SQL Console of the Web IDE. Working this way helps us to understand each individual step in the setup of replication.

For this approach, you first need to create a virtual table based on the source data. You also need a target table in SAP HANA Cloud.

Once you’ve created these objects, you can then work through the following steps:

Create the remote subscription to register the target table as the receiver of the source data.
Code Snippet
Copy code
Switch to dark mode
```
123
CREATE REMOTE SUBSCRIPTION <subscription_name> 
ON <virtual_table_name> 
TARGET <target_table_name>;
```
Queue the remote subscription (except for RTR), to create all the necessary objects in the source. (Triggers, Shadow tables, and so on.)
Code Snippet
Copy code
Switch to dark mode
```
1
ALTER REMOTE SUBSCRIPTION <subscription_name> QUEUE;
```
Do initial load (except for RTR), if you want to get the existing source data.
Code Snippet
Copy code
Switch to dark mode
```
12
‌INSERT INTO <target_table_name> 
(SELECT * FROM <virtual_table_name>);
```
Distribute the subscription to start the actual replication of modifications.
Code Snippet
Copy code
Switch to dark mode
```
1
ALTER REMOTE SUBSCRIPTION <subscription_name> DISTRIBUTE;
```

After adding some rows to the source, you can query the target table to check for the new data.

First, launch this video to see an example with a smart data integration (SDI) connection:

Now launch the following video to see an example using Remote Table Replication, using a smart data access SDA) connection:

Additional Features

You can choose to replicate only some specific columns, and/or partitions. We call this a subreplication.

You need to execute the following statements to create/alter the remote subscription:

For a subreplication based on columns:
Code Snippet
Copy code
Switch to dark mode
```
12
CREATE REMOTE SUBSCRIPTION <subscription_name> 
{AS SELECT (<column_list>) FROM <virtual_table_name> TARGET TABLE <target_table_name>};
```
For a subreplication based on partitions:
Code Snippet
Copy code
Switch to dark mode
```
12
ALTER REMOTE SUBSCRIPTION <subscription_name> 
{ PARTITIONS ( <partition_number> [ {, <partition_number> }...] ) | ALL PARTITIONS }
```

The other steps stay the same.

Launch this video to learn how to set up subreplication:

Replicating Data using SQL

SQL Statements to Manage Replication

Additional Features