Application Note for QuNect ODBC for QuickBase

Optimizing SQL Server OPENQUERY with INSERT, UPDATE and DELETE

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. Normally you can't use the OPENQUERY function with INSERT, UPDATE or DELETE statements. The advantage of OPENQUERY is that it passes the SQL statement you supply as its second argument to the linked server you supply as its first argument, directly without modification. Then it queries the ODBC driver for the number of columns returned in the result set. The problem is that DELETE, INSERT and UPDATE statements do not return any columns. This causes an error. QuNect ODBC for QuickBase has a special extended SQL syntax of DELETEOQ, INSERTOQ and UPDATEOQ that return a single phantom column called QuNectOpenQuery. There are always zero rows available in this result set. You can execute the standard INSERT, UPDATE or DELETE statements against a linked server by using the four part name syntax like this:

        UPDATE QUICKBASE."My QuickBase Application".."b6mpfsxfr" SET checkbox = 1 WHERE checkbox = 0
        

However the performance of this kind of statement is poor because SQL Server first does a complete scan of all rows and columns. The exception is the INSERT statement which performs as well using the four part name syntax or the OPENQUERY syntax. The QuNect ODBC for QuickBase driver supports an extended SQL syntax to allow the use of OPENQUERY with INSERT, UPDATE and DELETE statements. The above UPDATE statement can be passed to QuickBase like this:

        SELECT * FROM OPENQUERY(QUICKBASE, 'UPDATEOQ "b6mpfsxfr" SET checkbox = 1 WHERE checkbox = 0')
        

Notice that UPDATE has become UPDATEOQ. You can also use this technique with DELETE statements.

        SELECT * FROM OPENQUERY(QUICKBASE, 'DELETEOQ FROM "b6mpfsxfr" WHERE checkbox = 0')
        

Here's an example that allows you to pass in parameters at run time

        DECLARE @DateModifiedText nvarchar(128)
        DECLARE @DateModified datetime
        SET @DateModified = DATEADD(d,-60,Convert(datetime,GETDATE()))
        SET @DateModifiedText = (SELECT CONVERT(VARCHAR(23), @DateModified, 121))
        SET @DateModifiedText = '{ts '''''+ @DateModifiedText + '''''}'
        DECLARE @tempsql nvarchar(max)
        set @tempsql = 'select * from openquery(QUICKBASE,''DELETEOQ from "bc7sd8jg3" where where [Application _ Loan Status] like ''''%ACTIVE%'''' and [Date Created]<' + @DateModifiedText  + ' and [Type of File] Not Like ''''%Notarized%'''' '')'
        EXEC(@tempsql)
        

Notice that DELETE has become DELETEOQ. You can also use this technique with INSERT statements. However INSERT statements are not prone to the performance problems encountered with UPDATE and DELETE statements.