# Mass Inserts MySQL

**URL:** <https://forum.inductiveautomation.com/t/mass-inserts-mysql/9646>\
**Category:** Ignition\
**Created:** [February 3, 2015, 2:11pm UTC](https://forum.inductiveautomation.com/t/mass-inserts-mysql/9646 "2015-02-03T14:11:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![EricFrancoeur](https://avatars.discourse-cdn.com/v4/letter/e/dfb087/32.png) [@EricFrancoeur](https://forum.inductiveautomation.com/u/EricFrancoeur)\
**Post date:** [February 3, 2015, 2:11pm UTC](https://forum.inductiveautomation.com/t/mass-inserts-mysql/9646/1 "2015-02-03T14:11:48Z")

</div>

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???

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\
**Post date:** [February 3, 2015, 2:33pm UTC](https://forum.inductiveautomation.com/t/mass-inserts-mysql/9646/2 "2015-02-03T14:33:06Z")

</div>

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.