Application Note for QuNect ODBC for Quick Base

Slaving a Quick Base Table to a SQL Server Table with one SQL Statement

The basic idea here is that there exists a Quick Base 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 Quick Base. When an existing record in SQL Server changes then the intention is that the corresponding record in Quick Base 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 Quick Base table is not the built-in field called Record ID#. We'll call it QDBKey and will assume this key field in Quick Base 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", "FirstName", "LastName", "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 Quick Base. This might look something like this:

INSERT INTO QUICKBASE."ACME_Pipeline".."Customers_bbsd23jlm" ("QDBKey", "First_Name", "Last_Name", "Address", "City", "State", "Country") SELECT "SQLServerKey", "FirstName", "LastName", "Address", "City", "State", "Country" FROM dbo.Customers WHERE "DateModifiedinSQLServer" > @SQLServerLastModifiedTime

This INSERT statement obvously does not take care of deleting records in Quick Base that no longer exist in SQL Server. Also notice that the Quick Base field names have all nonalphanumeric charaters replaced with underscores. This is the default behaviour with QuNect ODBC for Quick Base 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.