cbastian-hill
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:
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 );
}
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>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. Why are you using XML?
2. Why not attach SQLite tables to your Access database and pump the data directly?
2. Why not attach SQLite tables to your Access database and pump the data directly?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
2. Does each user have their own copy of the front-end database?
3. Where does the data reside relative to the users?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
...
ASKER