ODBC Driver for Sage 50 UK

Build 24.0.8963

REPLICATE Statements

The REPLICATE statement makes it easy to maintain copies of one or many tables in a cache database. Each time the REPLICATE statement is invoked, the driver requests incremental changes that have occurred since the last time the cache database was updated.

The REPLICATE statement adds new rows, updates existing rows that have changed, and deletes rows that have been deleted. The REPLICATE statement can execute multiple underlying operations based on the API available from the data source. All operations are executed as a single transaction to maintain the consistency of the cached data.

REPLICATE Syntax

You can use REPLICATE statements to perform the following actions:

  • Replicate a single table: REPLICATE <table_name>
  • Replicate all tables:REPLICATE ALL
  • Replicate only the specified tables: REPLICATE TABLES
  • Replicate a query's results: REPLICATE <select_statement>. Use a SELECT statement to control the columns that are cached, as well their names, or to apply any SQL formulas before replicating data.

The EXCLUDE COLUMNS clause can be specified in all of these statements. The REPLICATE ALL statement additionally supports an EXCLUDE TABLES clause.

Here is the complete syntax :

REPLICATE 
{ 
  [ <cached_table_name> ] 
  [ EXCLUDE COLUMNS ( <column_name> [ , ... ] ) ] 
  { <select_statement> | <table_reference> } 
  (<column_definition> [ , ... ] [<table_constraint>])
  [WITH {<option_name>=<option_value>|<option_name>} , ... ]
  [AS] { <select_statement> | <table_reference> } 
}
| TABLES 
  ( 
    {
      <table_name> 
      [ EXCLUDE COLUMNS ( <column_name [ , ... ] ) ]
    } [ , ... ] 
  ) 
| ALL 
  [
    { 
      EXCLUDE COLUMNS ( { <table_name>.<column_name> } [ , ... ] )
      | EXCLUDE TABLES ( <table_name> [ , ... ] ) 
    }
  ]
  
<table_constraint> := PRIMARY KEY(<column_name>,...)
<option_name> := DropTable | TruncateTable | AlterSchema ...
<option_value> := <literal> | <identifier>

Common Queries

Use the following statement to maintain a copy of a table. This command creates a table TradingAccounts in the cache database if it does not already exist. If the table exists, the REPLICATE statement updates recent changes (newly updated, deleted, and inserted records) since the last cache update.

REPLICATE TradingAccounts

Use the following statement to replicate TradingAccounts to REP_TradingAccounts.

REPLICATE REP_TradingAccounts SELECT TradingAccountUUID, Name FROM TradingAccounts

Use the following statement to select specific columns and perform operations on data before it is replicated. This command creates the table REP_TradingAccounts with the columns DateModified and FullName. The FullName column is a concatenation of FirstName and LastName from the TradingAccounts table.

REPLICATE REP_TradingAccounts SELECT  DateModified, CONCAT(firstname," ",lastname) AS FullName FROM TradingAccounts
Note: Selecting fewer columns than what are already in the cache leads to stale data in some columns. The SELECT statement with a custom projection (changed column names/values, or specific columns) should remain the same throughout the life of the cache.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963