Incremental replicate Freshdesk to SQL Server

Wiki (en)
, , ,
1

Incremental replicate Freshdesk to SQL Server

Using Invantive SQL you can copy data from a platform such as Freshdesk to your own SQL Server database with solely one statement such as:

create or replace table contacts@sql
as
select * 
from   contacts@freshdesk

This creates a table on SQL Server with data types that are able to fit the Invantive SQL data types, adds indexes where justifiable based upon the data being loaded and then bulk inserts the data.
However, this approach retrieved all data each time and can be a major burden in terms of run-time as well as burden placed on the Freshdesk API servers.

You can also use an incremental approach which just adds new data to your SQL Server database. Disadvantage is that when you delete records in Freshdesk these deletes might go unnoticed.
The following Invantive PSQL on initial use creates the table on SQL Server and then on every next execution adds new rows:

declare
  l_cnt pls_integer;
begin
  select count(*)
  into   l_cnt
  from   SYSTEMTABLES@DataDictionary
  where  name = 'tickets'
  and    data_container_alias = 'sql'
  ;
  if l_cnt = 0
  then
    execute immediate 'create or replace table tickets@sql as select * from tickets@freshdesk';
  else
    --
    -- Incremental load of tickets. Run at least every 30 days.
    --
    execute immediate 'create or replace table oldtickets@inmemorystorage as select * except rowid$ from tickets@sql';
    execute immediate 'create or replace table tickets@sql as select * from recent_tickets@freshdesk union distinct on id select * except rowid$ from oldtickets@inmemorystorage';
  end if;
end;

Recent Tickets

Note that Freshdesk has yet no modified by date filters (some are in beta), but the recent_tickets table includes solely the tickets of the last 30 days.