Eliminating Integration Reads When Keeping a Quickbase table in Sync with an on Premises Table

Application Note for QuNect ODBC for QuickBase

How to use INSERTs and DELETE's to synchronize a table that originates from your on premises system, with a copy of that table in Quickbase. This technique uses no integration reads. To see an example of this in practice please read Mirroring the Schema of a Single SQL Server Table in Quickbase. You can optionally even delete records in Quickbase after corresponding records are deleted from your on premises table. Although demonstrated with a SQL Server linked server to Quickbase, this technique applies to any other environment including SSIS (SQL Server Integration Services), Python, C#, VB.Net, etc. Best practices indicate that you want to have your Quickbase key field set to match the key field in your on premises table. Quickbase makes it easy to switch the key field from the default "Record ID#" column to any other column. This is true even if the table is in one to many relationship as the parent table. Because Quickbase doesn't support compound keys, if your on premises table has a compound key, you will need to synthesize a single key column for your on premises table with a concatenation of the key fields.

CONCAT(keyFieldOne, '-', KeyFieldTwo)

If your key fields are not text fields you may have to convert them to text like this:

CONCAT(CONVERT(varchar, keyFieldOne), '-', CONVERT(varchar, keyFieldTwo))

            
USE [QuNect]
GO
DECLARE @deleteGUID uniqueidentifier
SET @deleteGUID = NEWID()
DECLARE @tempSQL nvarchar(max)
INSERT INTO [QUICKBASE].[IT_Ticket_Help_Desk]..[IT_Ticket_Help_Desk__Activities_bunepj7pv]
            ([Activity_Type]
            ,[Notes]
            ,[Scheduled_Activity_Date]
            ,[Scheduled_Start_Time]
            ,[Duration__mins_]
            ,[Scheduled_End_Time]
            ,[Scheduled_Start_Date___Time]
            ,[Scheduled_End_Date___Time]
            ,[Scheduled_For]
            ,[Schedule_Status]
            ,[iCalendar]
            ,[Scheduled_Activity_Subject]
            ,[Meeting_Info___Location]
            ,[Activity_Date]
            ,[Created_By]
            ,[Record_ID_]
            ,[Related_Ticket]
            ,deleteme)

SELECT [Activity_Type]
        ,[Notes]
        ,[Scheduled_Activity_Date]
        ,[Scheduled_Start_Time]
        ,[Duration__mins_]
        ,[Scheduled_End_Time]
        ,[Scheduled_Start_Date___Time]
        ,[Scheduled_End_Date___Time]
        ,[Scheduled_For]
        ,[Schedule_Status]
        ,[iCalendar]
        ,[Scheduled_Activity_Subject]
        ,[Meeting_Info___Location]
        ,[Activity_Date]
        ,[Created_By]
        ,CONVERT(bigint, [Activity_ID])
        ,CONVERT(bigint, [Related_Ticket])
        ,CONVERT(varchar(36), @deleteGUID) 
    FROM [dbo].[Activities]
    SET @tempSQL = 'DELETEOQ FROM bunepj7pv WHERE deleteme <> ''' + CONVERT(nvarchar(36), @deleteGUID) + ''''
    PRINT @tempSQL
    SET @tempSQL = 'SELECT * FROM OpenQuery(QUICKBASE, ''' + REPLACE(@tempSQL,  '''', '''''') + ''')'
    PRINT @tempSQL
    EXEC(@tempSQL)
    GO