# How do you write a dataset to SQL? **URL:** \ **Category:** Ignition\ **Created:** [May 18, 2012, 6:08pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497 "2012-05-18T18:08:01Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\ **Post date:** [May 18, 2012, 6:08pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/1 "2012-05-18T18:08:01Z")
I have not had to write more than one row at a time to MSSQL from Ignition and I want to make this is done correctly. I have a calculated dataset about 1,000 rows long that I want to write to SQL. My columns are: block\_id xVal yVal Do I have to iterate through the dataset writing one row at a time? `def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id") for row in pyData: xVal = row["distance"] yVal = row["height"] system.db.runPrepUpdate("INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES (?,?,?)", [block_id, xVal, yVal], "PMIServer") return ` or can I pass the dataset to it like this? `def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id") newData= [] for row in pyData: xVal = row["distance"] yVal = row["height"] newData.append([block_id, xVal, yVal]) headers = ["block_id", "xVal", "yVal"] dsRollData = system.dataset.toDataSet(headers, newData) system.db.runPrepUpdate("INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES (?,?,?)", [dsRollData], "PMIServer") return` I welcome any other comments you may have. Thanks, ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [May 18, 2012, 6:50pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/2 "2012-05-18T18:50:41Z")
You can’t pass in an array to a single SQL query. However, you can do something like this:[code]def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block\_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block\_id") valueString = “” args = [] for row in pyData: xVal = row[“distance”] yVal = row[“height”] valueString += “(?,?,?),” args.append(block\_id) args.append(xVal) args.append(yVal) if valueString != “”: valueString[:-1] system.db.runPrepUpdate(“INSERT INTO GuageBand\_RollData (block\_id, xVal, yVal) VALUES %s” % valueString, args, “PMIServer”) return[/code]It builds one insert query that looks something like:`INSERT INTO GaugeBand_RollData (block_id, xVal, yVal) VALUES (?,?,?), (?,?,?), (?,?,?).......`You can also use transactions:[code]def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block\_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block\_id") txId = system.db.beginTransaction(timeout=5000) for row in pyData: xVal = row[“distance”] yVal = row[“height”] system.db.runPrepUpdate(“INSERT INTO GuageBand\_RollData (block\_id, xVal, yVal) VALUES (?,?,?)”, [block\_id, xVal, yVal], “PMIServer”, txId) system.db.commitTransaction(txId) system.db.closeTransaction(txId) return[/code]The first one is more efficient because it only calls one system.db function. Each function call uses around 80 ms to talk to the gateway. The second one uses more calls so it will take longer. ---
**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\ **Post date:** [May 18, 2012, 7:08pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/3 "2012-05-18T19:08:27Z")
That looks good, I will try it. I have another function that writes these same values to the PLC. I build the lists then use: ```auto system.opc.writeValues(opcServer,rollItemPaths,rollValues) ``` It takes about 10 seconds to write all of the values. Now that I will be writing this to SQL, will I get better performance by waiting until the data is written to SQL and then use a block write to the PLC from a transaction group? ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [May 18, 2012, 7:16pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/4 "2012-05-18T19:16:28Z")
Yes, you would. You get better performance when you have less function calls from the client to the server. Transaction groups run on the server so they are always fast. ---
**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\ **Post date:** [May 21, 2012, 9:57pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/5 "2012-05-21T21:57:38Z")
It looks like I am trying to pass too much data in one call. Any suggestions on how to get around this without having to make 1000 calls? Would it be advisable to somehow split this into several chunks of data? [quote]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. [/quote] ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [May 21, 2012, 11:11pm UTC](https://forum.inductiveautomation.com/t/how-do-you-write-a-dataset-to-sql/4497/6 "2012-05-21T23:11:12Z")
If there is a max you can’t get all of it into a single call. I would have a counter that maxes out at 2100 and runs the query, resets and goes for another 2100. So if you have 4201 you will get three calls. That is much better than 4201.