opervebpary
New Member
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:\[code\]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 );}\[/code\]Here is an example of the XML that I am reading into the database:\[code\]<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>\[/code\]