Mass Inserts MySQL

Ignition
1

Hello,

Well I have to insert a 1000 to 10000 rows array (dtaMSSQLAllPieces) with a wooping 7 columns into my MySQL database (ProdRestigouche)

I’m testing the runPrepUdpate with a Transaction Token and the behavior seems to be the same as the ruPrepUpdate by itself…

Here’s what I’ve got so far…

The MessageBox is just as a process time identifier, and I’ve notice that the data is written into my database before the commit MessageBox. To what I understand, the data shouldn’t be written to the database before the commit… but I’ve been wrong before.

I guess my question is that why is it still writting to my database row per row (indexing each row and blah blah blah for ever) instead of a nice bulk insert?

And is there a better way to do this???

txId = system.db.beginTransaction(timeout=120000)
	
	for row in dtaMSSQLAllPieces:
		qryInsertRow = """
			INSERT INTO tblPieceAutologTemp 
				(IDDetail, GradeNameOut, IDDetailShift, RunName, SortTbl, SpecieName, Pile) 
			VALUES (?,?,?,?,?,?,?)
		"""
		system.db.runPrepUpdate(qryInsertRow, [row['IDDetail'], row['GradeNameOut'],   row['IDDetailShift'], row['RunName'], row['SortTbl'], row['SpecieName'], row['Pile']], "ProdRestigouche", txId)
	
	system.gui.messageBox("Committing")	
	system.db.commitTransaction(txId)
	system.db.closeTransaction(txId)
2

Try something like this:

[code]txId = system.db.beginTransaction(timeout=120000)

valString = “(?,?,?,?,?,?,?)”
allValues = []

for row in dtaMSSQLAllPieces:
allValues.extend([row[‘IDDetail’], row[‘GradeNameOut’], row[‘IDDetailShift’], row[‘RunName’], row[‘SortTbl’], row[‘SpecieName’], row[‘Pile’]])

qryInsertRow = “”"
INSERT INTO tblPieceAutologTemp
(IDDetail, GradeNameOut, IDDetailShift, RunName, SortTbl, SpecieName, Pile)
VALUES %s
“”"%(",".join([valString]*(len(allValues)/7)))
system.db.runPrepUpdate(qryInsertRow, allValues,“ProdRestigouche”, txId)
system.db.commitTransaction(txId)
system.db.closeTransaction(txId)[/code]

You may have to add some additional code to split it up into smaller chunks.