• Welcome, Guest. Please login.
August 15, 2020, 10:42:08 am


Welcome to the SQLitening support forums!

How do I write a group of records at one time

Started by Marty Francom, February 22, 2011, 05:03:22 pm

Previous topic - Next topic

Marty Francom

I have a function that loops through record gathering records that need changes.

Currently I find a record anthe make the change to the record.  This works OK but
is reather slow.   I would rather gather all records that need changes then make one
call to SQLite to write the records.  I have tried to do this but I must be doing someting
wrong.   From this code snippet, can you tell me what I am doing wrong?

             'rc = slExe (slBuildInsertOrUpdate(sTbl, wk, lDgF, wStr), "E")
             allRecs = slBuildInsertOrUpdate(sTbl, wk, lDgF, wStr)
             cnt2 += 1 
             sTxt = Parse$(DrgRec, Chr$(127), 5) & "!"
        rc = slExe (allRecs,  "E") 

Do I need to insert a "separator" between record sets?

R Robinson

Something like below

If Where is omitted or empty then will build an Insert statement else will build an Update statement appending " Where " and the Where value. Columns contains a comma delimited list of the column names. Column names are optional for Insert but required for Update.

            Local fstr        As String        'the fields string
            Local vstr       As String        'the values string nuls between each field except for last
            Local wstr      As String        'the where string
'use ROWID where possible to help with insert speed
slexe "BEGIN"
   for i = 1 to RECCOUNT
'in here set variables to values needed for update
            fStr = "MANUF,REDREF,PRODUCT,TYPE"
            vStr = MyCompany & $NUL & REFNUM & $NUL & _
                        MyProduct & $NUL & MyType
            wStr = "rowid = " & rownum
            slExe slBuildInsertOrUpdate ("Parts", vstr, fStr, wStr)
  next i
slexe "END"

Rolf Brandt

I like to cook with wine - sometimes I even add it to the food.

Fred Meier

There are two ways to speed up inserting/updating multi records:
1. Stack you Insert/Update as Rolf posted.
2. For the "fastest" use slExeBind.  Excerpt from slExeBind doc:
QuoteYou may use slExeBind to affect one or multiple records.  The default is to
   affect only one record.  Add the V ModChar (see below) to affect multiple
   records.  Using the V ModChar is a highly effecent/fast way to insert or
   update many records.  Using slExeBind this way tells SQLite3 to only
   prepare/compile the SQL statement one time and then affect each row re-using
   the prepared/compiled statement. See the example below and also look at
   The following example will insert four columns into a single record:
     The column types are --
     1 = Blob, 2 = Compressed Text, 3 = Compressed Encrypted Blob, 4 = Double .
        slExeBind "Insert into T1 values(?, ?, ?, ?)", _
                                         slBuildBindDat("This is some Blob data") & _
                                         slBuildBindDat("This is some compressed Text", "TC") & _
                                         slBuildBindDat("This is a compressed and encrypted Blob", "CE") & _
                                         slBuildBindDat("123.456", "D")
   The following example will insert three columns into 50 records:
     The column types are --
     1 = Ingeger 32 bit, 2 = Null, 3 = Integer 64 bit
        dim lsaRows(1 to 100) as String
        for llDo = 1 to ubound(lsaRows)
           lsaRows(llDo) = iif$(llDo mod 2, slBuildBindDat(format$(llDo), "i"), slBuildBindDat(format$(llDo + 1000^4), "I"))
        slExeBind "Insert into T1 values(?, Null, ?)", join$(lsaRows(), ""), "V2"
     Note the use of an array and the join$ command which is much faster then
     standard concatenation.