Application Note for QuNect ODBC for QuickBase

Mirror 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 mirror 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 changes 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# or that there is a field whose unique property has been checked. Whether it's the key field or just a field whose unique property is checked, we'll call it QDBKey. We will assume this field called QDBKey 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 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", "FirstName", "LastName", "Address", "City", "State", "Country" FROM dbo.Customers WHERE "DateModifiedinSQLServer" > @SQLServerLastModifiedTime

This INSERT statement obviously 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 non-alphanumeric characters replaced with underscores. This is the default behavior 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.