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;
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.