Need a faster way to input A LOT of records in SQLite db

[sshrum]

I'm looking to see if I can just construct a large string with everything in it and then flush it to the db with the INSERT command.

Possible? How do I struct the string?

TIA

EDIT: did some SQL research and just tried something like this:

Insert into xxx (field1, field2) VALUES ("11", "12"), ("21", "22"), ..., ("91","92")

No joy. Doesn't work. Seems to not like the ',' after the ")"s

Seems to work fine if I do it one at a time but that's too slow.

Edited October 16, 2007 by sshrum

[Shevilie]

2.41 secs per insert sounds wrong... wheres you db placed ??

Try to do it like this

open connection to db
$begin = TimerInit()
Insert into xxx (field1, field2) VALUES ("11", "12")
$dif = TimerDiff($begin)
MsgBox(0,"Time Difference",$dif)
close connection to db

[randallc]

Hi,

try begin, commit, and then just one sqlexe?

In old versions, I pulled a func;

Func _SQL_CreateRandomDb(ByRef $sDB, ByRef $sDbTable1, $TotalRandomRows, $MaxStringCreate = 100000)
    $sDbTable1 = "test"
    Local $sErrorMsg, $a;
    If Not FileExists($sDB) Then $sDB = FileGetShortName(@ScriptDir & "\" & "sqlite.db3")
    $sDB = FileGetShortName($sDB)
    If FileExists($sDB) Then FileDelete($sDB)
    $nTimerQuery = TimerInit()
    Local $sInput, $iRval, $sOutput
    $sInput = "DROP TABLE IF EXISTS " & $sDbTable1 & " ;"
    $sInput &= "CREATE  TABLE " & $sDbTable1 & "( a INTEGER PRIMARY KEY, b TEXT, c INTEGER, d FLOAT );" & @CRLF
    $sInput &= "DROP INDEX IF EXISTS idxD ;"
    $sInput &= "CREATE INDEX idxD ON " & $sDbTable1 & "(d);" & @CRLF
    _SQLiteExe($sDB, $sInput, $sOutput)
    $TotalRandomRowsINT = Int(($TotalRandomRows) / $MaxStringCreate)
    $s_Insert = "a , b, c , d"
    For $j = 0 To $TotalRandomRowsINT
        $sInput = "BEGIN TRANSACTION;" & @CRLF
        $BaseStringLength = $MaxStringCreate * $j
        For $i = $BaseStringLength + 1 To _Iif($TotalRandomRows> ($BaseStringLength + $MaxStringCreate), $BaseStringLength + $MaxStringCreate, $TotalRandomRows)
            $s_ValueLine = "Data:" & $i & "','" & Random(-10, 10, 1) & "','" & Random(1, 10)
            $sInput &= "INSERT INTO " & $sDbTable1 & "( b, c, d) VALUES('" & $s_ValueLine & "');"
        Next
        $sInput &= "COMMIT TRANSACTION;" & @CRLF
        _SQLiteExe($sDB, $sInput, $sOutput)
        ConsoleWrite('@@ Debug(193) : ' & "createData" & $i & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
    Next
    ConsoleWrite('@@ Debug(193) : ' & "createData" & $i & "=" & Round(TimerDiff($nTimerQuery), 2) & " MSEC." & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
EndFunc   ;==>_SQL_CreateRandomDb

No longer working, but gives the idea?

does that help?

best, randall

[sshrum]

Begin/Commit worked.

A bit redundant having to pass the fields all the time, but whatever.

Sorta surprised that the normal way didn't work. Coulndn't find anything on the sqlite site to say that it was r wasn't supported.

Thanx