Application Note for QuNect ODBC for QuickBase
Copying Records from SQL Server to QuickBase
If you don't already have a linked server called QUICKBASE, create one using the instructions in the Creating a Linked Server for QuickBase in SQL Server Using the UI application note. You can get a list of all QuickBase tables you have access to by following the instructions in the Listing All QuickBase Tables in SQL Server application note. Let's say one of your QuickBase tables is called Acme Pipeline: Customers bbsd23jlm. Let me decode this name for you. The first part of the name extends all the way to the first colon character. Acme Pipeline is the name of your QuickBase application. A QuickBase application can contain one or more tables. This application has at least one table in it called Customers. The last part of the name is the DBID of the table. The DBID uniquely identifies the table amongst all tables (yours and everyone elses) in QuickBase. Now let's say you have a table in SQL Server of customer information using the same field names in QuickBase. To copy all the fields of all the rows in the SQL customer table called: Customers you can run the following SQL. This SQL statement uses the four part name convention. This first part of the name is the linked server. The second part is the catalog on the linked server where the table resides. Catalogs equate to QuickBase applications. The third part of the name is left blank. And the last or fourth part of the name is the DBID of the QuickBase table.
INSERT INTO QUICKBASE."ACME_Pipeline".."bbsd23jlm" ("First_Name", "Last_Name") SELECT "First Name", "Last Name" FROM dbo.Customers
Unfortunately due to a limitation of the OLE DB Provider for ODBC that Microsoft supplies with SQL Server, SQL Server cannot update or insert into QuickBase Multi-line text fields (BLOB columns). As a result the QuNect ODBC for QuickBase driver automatically sets the size of all multi-line text fields to 8000. This ensures that by default all text fields appear to SQL Server to be non BLOB columns.