Application Note for QuNect ODBC for QuickBase

Slaving a QuickBase Table to a SQL Server Table with one SQL Statement

The basic idea here is that there exists a QuickBase table that is a slave to a table in SQL Server. Records originate in SQL Server and are periodically pushed out from SQL Server to QuickBase. When an existing record in SQL Server changes then the intention is that the corresponding record in QuickBase should be updated to reflect those chenges when the next push is made. The pushing of new records and updating of new records can be accomplished with a single INSERT statement! This application note assumes that there is a single field that uniquely identifies each record in SQL Server. For the purposes of this application note we will call this field SQLServerKey. We will also assume that the key field of the QuickBase table is not the built-in field called Record ID#. We'll call it QDBKey and will assume this key field in QuickBase is of the same type or a compatible type to the SQL Server field called SQLServerKey. The INSERT statement will look like this:

INSERT INTO QUICKBASE."ACME_Pipeline".."Customers_bbsd23jlm" ("QDBKey", "First_Name", "Last_Name", "Address", "City", "State", "Country") SELECT "SQLServerKey", "First Name", "Last Name", "Address", "City", "State", "Country" FROM dbo.Customers 

You can add a WHERE clause to the SQL statement above so that only records that have been modified since the last synchronization will be sent over to QuickBase. This might look something like this:

INSERT INTO QUICKBASE."ACME_Pipeline".."Customers_bbsd23jlm" ("QDBKey", "First_Name", "Last_Name", "Address", "City", "State", "Country") SELECT "SQLServerKey", "First Name", "Last Name", "Address", "City", "State", "Country" FROM dbo.Customers WHERE "Date Modified in SQL Server" > @SQLServerLastModifiedTime

This INSERT statement obvously does not take care of deleting records in QuickBase that no longer exist in SQL Server. Also notice that the QuickBase field names have all nonalphanumeric charaters replaced with underscores. This is the default behaviour with QuNect ODBC for QuickBase as it prevents problems with interfacing with other database systems like SQL Server that are not as liberal with the characters that are allowed in field names.