Link to home
Start Free TrialLog in
Avatar of cbastian-hill
cbastian-hillFlag for United States of America

asked on

Inserting well over 100,000 records into SQLite...

I have a database that needs to be updated periodically. My employer wants to keep a base instance of it in MS Access, and update one in a local SQLite table.

I've been able to grab all of the data out of the MS Access table (into XML), but when I try to insert it into the SQLite table, I get the error that there are too many terms in the compound select statement.

I know that SQLite limit compound select inserts to 500, but this database is over 150,000 rows. I'm at a loss for how to get the data moved over.

Anyone have any thoughts?

Here is the code that I was trying to use:
var sqlItem:String="INSERT INTO items (itemID,barcode,desc,brandNum,size,units,multQty,multPrice,price,brand,cat01,catSub,cost,srp,lastPriceChangeDate,lastScanDate,addDate,chgDate) ";
for each(var i:XML in itemList.item){
    sqlItem=sqlItem+"SELECT "+
    parseInt(i.itemID,10)+","+
    parseInt(i.barcode,10)+","+
    "\""+i.description+"\","+
    parseInt(i.brandNum,10)+","+
    "\""+i.size+"\","+
    "\""+i.units+"\","+
    parseInt(i.multQty,10)+","+
    "\""+i.multPrice+"\","+
    "\""+i.price+"\","+
    "\""+i.brand+"\","+
    "\""+i.cat01+"\","+
    "\""+i.catSub+"\","+
    "\""+i.cost+"\","+
    "\""+i.srp+"\","+
    "\""+i.lastPriceChangeDate+"\","+
    "\""+i.lastScanDate+"\","+
    "\""+i.addDate+"\","+
    "\""+i.chgDate+"\""+
    " UNION ";
}
sqlItem=sqlItem.substring(0,sqlItem.length-7);
itemStmt.text=sqlItem;
try{
    itemStmt.execute();
}catch(error:SQLError){
    trace("Update USER Database - ERROR: "+error.detailID +" - "+error.details );
}

Open in new window

Here is an example of the XML that I am reading into the database:
<items>
    <item>
        <itemID>1234</itemID>
        <barcode>01111111111111</barcode>
        <description>Product Description</description>
        <brandNum>1</brandNum>
        <size>1</size>
        <units>oz.</units>
        <multQty>1</multQty>
        <multPrice>0.85</multPrice>
        <price>0.85</price>
        <brand>Product Brand</brand>
        <cat01>Product Category</cat01>
        <catSub>(none)</catSub>
        <cost>0.10</cost>
        <srp>0.95</srp>
        <lastPriceChangeDate>1/9/2009 3:32:29 PM</lastPriceChangeDate>
        <lastScanDate>1/9/2009 3:32:29 PM<lastScanDate>
        <addDate/>1/9/2009 3:32:29 PM<addDate/>
        <chgDate>1/9/2009 3:32:29 PM</chgDate>
    </item>
    <item>
        <itemID>1234</itemID>
        <barcode>01111111111111</barcode>
        <description>Product Description</description>
        <brandNum>1</brandNum>
        <size>1</size>
        <units>oz.</units>
        <multQty>1</multQty>
        <multPrice>0.85</multPrice>
        <price>0.85</price>
        <brand>Product Brand</brand>
        <cat01>Product Category</cat01>
        <catSub>(none)</catSub>
        <cost>0.10</cost>
        <srp>0.95</srp>
        <lastPriceChangeDate>1/9/2009 3:32:29 PM</lastPriceChangeDate>
        <lastScanDate>1/9/2009 3:32:29 PM<lastScanDate>
        <addDate/>1/9/2009 3:32:29 PM<addDate/>
        <chgDate>1/9/2009 3:32:29 PM</chgDate>
    </item>
</items>

Open in new window

SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cbastian-hill

ASKER

kaufmed, I tried that...  There are just too many records, and it takes way too long...  Sometimes locking up the system.
1. Why are you using XML?  
2. Why not attach SQLite tables to your Access database and pump the data directly?
Aikimark,  I'm not aware of a way to do that.  Could you point me in the right direction?  That would be an optimal solution, if it is possible.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Aikimark, I see what that is doing.  That won't really work, because this system will be used from multiple computers, and the access database will exist on a different system.  Thanks for the idea, though.
1. Is your Access application split between front-end and back-end database files?
2. Does each user have their own copy of the front-end database?
3. Where does the data reside relative to the users?
Aikimark, there is no Access application.  This is a Flex/AIR application.  My employer simply wants to keep their master database in their old Access database.  The program will be using local SQLite, and a remote MySQL database.  The problem is getting all of the data from the Access database.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...