# Insert Dataset into SQL Table **URL:** \ **Category:** Ignition\ **Created:** [April 21, 2016, 8:45pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557 "2016-04-21T20:45:09Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![snowgoer](https://avatars.discourse-cdn.com/v4/letter/s/f9ae1b/32.png) [@snowgoer](https://forum.inductiveautomation.com/u/snowgoer)\ **Post date:** [April 21, 2016, 8:45pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/1 "2016-04-21T20:45:09Z")
So, I’ve searched high and low for the right code to complete this task…I’ve got a feeling I’m close, but I’m still missing something. Still a fresh fish when it comes to scripting. Here’s the situation…I’m gathering information from one dataset ‘ClassRoster’ and using one it to generate another dataset for the SQL insert statement. The second dataset gets created correctly from the code included, but what does the SQL Insert code look like to finish the job? I’m trying to insert the 2nd dataset ‘oneRow’ into a SQL table. Thanks for any help! [code]data = event.source.parent.getComponent(‘ClassRoster’).data table = event.source.parent.getComponent(‘Table’) rows = [] headers = [“EmployeeID”,“WstrnGroup”,“TrngGroupName”,“RecordDate”,“TrngChecklistCompLink”,“LockUser”,“InactiveFlag”,“TrngSkillLevel”,“DeptNum”] for row in range(data.rowCount): oneRow = [data.getValueAt(row, “EmployeeID”),event.source.parent.getComponent(‘WstrnGroup’).selectedStringValue,event.source.parent.getComponent(‘TrngGroupName’).selectedStringValue,event.source.parent.getComponent(‘ClassDate’).date,event.source.parent.getComponent(‘LabelLinkPath’).text,“NO LOCK”,0,event.source.parent.getComponent(‘SkillLevel’).selectedValue,event.source.parent.getComponent(‘Comments’).text] # system.db.runPrepUpdate(“INSERT INTO TrngRecords (oneRow) VALUES (?,?,?,?,?,?,?,?,?)”, [EmployeeID, TrngGroupName, WstrnGroup, RecordDate, TrngSkillLevel, TrngChecklistCompLink, DeptNum, LockUser, InactiveFlag]) rows.append(oneRow) data = system.dataset.toDataSet(headers,rows) table.data = data[/code] ---
**Author:** ![cbabb](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cbabb/32/49029_2.png) [@cbabb](https://forum.inductiveautomation.com/u/cbabb)\ **Post date:** [April 22, 2016, 2:10pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/2 "2016-04-22T14:10:51Z")
I have to do something similar… I needed to get all the data from a foxpro database and insert all of the records into a SQL table This is how I did it. [code] # DATA INITIALIZATION # Run once to initially populate the data in the SQL Database # first we get all of the LIMS data with a query to the LIMS database to return all records into a client dataset tag LIMSData = system.tag.read(“MOS1B Fractionator/Raw Test Data From LIMS”).value pdsLIMSData = system.dataset.toPyDataSet(LIMSData) # iterate through the entire dataset and pull out each individual row of data. Write the row data to the MS SQL Database for index in range(len(pdsLIMSData)): LIMSrowData = list(pdsLIMSData[index]) ``` system.db.runPrepUpdate("INSERT INTO MOS1B_Fractionator_Raw_LIMS_Data (UniqueID, Barcode, Test_Date, Test_Time, Mat_Code, Mat_Descr, Test_Code, Test_Descr, Results) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", LIMSrowData, "Inductive_Automation_Ignition_New")[/code] ``` ---
**Author:** ![snowgoer](https://avatars.discourse-cdn.com/v4/letter/s/f9ae1b/32.png) [@snowgoer](https://forum.inductiveautomation.com/u/snowgoer)\ **Post date:** [April 22, 2016, 3:25pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/3 "2016-04-22T15:25:23Z")
cbabb…thanks…works like a charm ---
**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\ **Post date:** [April 22, 2016, 3:48pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/4 "2016-04-22T15:48:28Z")
The Power Scripting Module provides a function for inserting a dataset into a database table. It could be done like this: `table = event.source.parent.getComponent('Table') pa.db.insertRows("MOS1B_Fractionator_Raw_LIMS_Data", table.data) ` Check it out: [doc.perfectabstractions.com/modu … insertrows](http://doc.perfectabstractions.com/modules/powerscripting/index.html#scripting-functions-db-insertrows) Best, ---
**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\ **Post date:** [July 18, 2018, 4:36pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/5 "2018-07-18T16:36:48Z")
I want to use the data in my table and then insert the rows but trigger based on time. I can use a gateway time based script to call my script. My table is populated from a dataSet tag, should i use that as my source for `pa.db.insertRows` ? ---
**Author:** ![waker1der](https://avatars.discourse-cdn.com/v4/letter/w/dbc845/32.png) [@waker1der](https://forum.inductiveautomation.com/u/waker1der)\ **Post date:** [July 18, 2018, 8:13pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/6 "2018-07-18T20:13:43Z")
to answer my own question ```auto dataSet = system.tag.getTagValue("tagpath") mpds = pa.dataset.toData(dataSet) pa.db.insertRows("tableName",mpds) ``` ---
**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\ **Post date:** [July 18, 2018, 8:38pm UTC](https://forum.inductiveautomation.com/t/insert-dataset-into-sql-table/11557/7 "2018-07-18T20:38:44Z")
Yep, looks good. tttttttttttttttttttttttt