Insert Select Query results into another DB Table

Ignition
1

I have a named query using parameters to make a select that returns multiple rows, for example:

Query.JPG1384×254 44.7 KB

I want to insert the results into another table, how do I pass the results one row at a time into the insert query?
my code so far:

vars = {"style":style, "size1":size1, "size2":size2, "material1":material1, "material2":material2}

		
results = system.db.runNamedQuery("BOM/WorkOrder",vars)

		
for index,row in enumerate(results):
         system.db.runNamedQuery("BOM/Insert",{})

Thanks

2

I didn’t want to do this, but I guess I should turn the query results into a pydataset and loop through it.

3

You were going to have to loop anyway. :slight_smile:

I have no idea how your named queries are set up, but if you are using the same column names from your first query to the second, you may be able to use it to your advantage:

vars = {"style":style, "size1":size1, "size2":size2, "material1":material1, "material2":material2}
    
results = system.dataset.toPyDataSet(system.db.runNamedQuery("BOM/WorkOrder",vars))

# Get column names from PyDataSet (comes in a s a list)
columnNames = results.getUnderlyingDataset().getColumnNames()

for row in results:
  # Create dictionary using the list of columnNames 
  # and the list of the row values
  rowDict = dict(zip(columnNames, list(row)))

  system.db.runNamedQuery("BOM/Insert", rowDict)
2 Likes
4

I figured the loop was the answer. I’ve got it working now, thanks

1 Like