Copy from one SQL table to another

Ok, I am looking for some insight on the best way to copy some data from one table and put it into another one. I am trying to write a gateway tag change script that will take CoatNumber, RobotNumber, DryingRoom, MinDryTime, MaxDryTime, and ManualPick from my “coats” table and then insert it into my “production” table.

The wrinkle I can not seem to get past is that when inserting into my “production” table I need to also insert HangerNumber with it which I will be getting from an OPC connection.

I would use SQL of the following form with runPrepUpdate() or an equivalent Named Query:

INSERT INTO "production" ("column1", "column2"...., "hangernumber")
SELECT "column1", "column2"...., ? As "hangernumber"
FROM "coats"
WHERE .....
ORDER BY .....

I tried this approach before posting and was hung up on how to deal with HangerNumber on the select side of things. To make sure I am understating, the AS keyword lets me alias the ? to HangerNumber in the production table. So then I would just setup my runPrepUpdate() with the proper variable that I would get from the tag.

Yes. Strictly speaking, you could give it any name in the SELECT. The list of columns to INSERT just has to be 1:1 with the expressions in the SELECT.

1 Like

This was the part that was hanging me up. Thank you so much. this is much simpler then the path a had started going down.

Ok last question, I am getting the following error:

java.sql.SQLException: No value specified for parameter 3

Here’s my code:

system.db.runPrepUpdate("INSERT INTO production(HangerNumber, CoatNumber, RobotNumber, DryingRoom, MinDryTime, MaxDryTime, ManualPick) SELECT (?) AS HangerNumber, CoatNumber, RobotNumber, DryingRoom, MinDryTime, MaxDryTime, ManualPick FROM coats WHERE recipes_id = (?) VALUES (?,?)", ['1','16'], "foxall")

I am sure it is something stupid I just cant see it. On a side note, is there a way to format the query part so it is easier to read?

You have four question marks, but two supplied values. The “VALUES (?, ?)” shouldn’t be there.

I usually place the sql into a script module constant using triple-quoted multiline strings.

1 Like

I know it’s been a while since someone replied to this thread. But right now I need to copy the data from one database to another (identical) database by pressing a button from the supervision. could someone give me some clue? I tried to do a procedure on the base, but I have no idea how to script access the base to activate the copy. I appreciate your help.

If they are truly separate databases that cannot be connected directly to each other, you will need a script that SELECTs the data from one database, then loops through the rows to INSERT to the other database.

Thanks,
I have to do this script in a Named Queries? or directly in the button?
Thanks again

It could be directly in the button. You will need legacy query security bypasses (project property) if so. If the number of rows is significant, you’d want to trigger this to run in a gateway message handler so that the pace of row inserts isn’t exacerbated by client comms latency.

Thanks,
But I want to clarify my problem:

This is my table (supervision), which is connected to my table N1a in the database:

Here is my Database:

When I press the validation button, all the information in table N°1 must be transferred to data table N°2 (in other words, data table N2 should only do one update).


I have made this little script however it doesn’t work:

When you said: “You will need legacy query security bypasses (project property) if so”, could you be more explicity. ?

Is mandatory un loop?
FYI: It’s a little table. No so much rows (as you can see in the screenshots.

Thanks again

Ok. There are two general approaches to perform this task. The best option in most cases is to make the database do the work. Since you didn’t provide actual queries, I’ll do generic examples. I’m going to assume your select query for table N1a looks something like this, as a named query:

SELECT column_a, column_b, column_c, column_d
FROM  n1a
WHERE column_e = :column_e_param
ORDER BY column_a

… and that the select query for N2 is the same, but from table n2.

The simplest solution for almost any database is an update query like this, as a named query:

INSERT INTO n2 (column_a, column_b, column_c, column_d, column_e)
SELECT column_a, column_b, column_c, column_d, column_e
FROM  n1a
WHERE column_e = :column_e_param

Note that this is nearly the same as the select query for N1a, but includes any other data from N1a that is needed in N2 that you aren’t normally displaying. The database does all the work in this form, and doesn’t even transfer the row data back to you. Just the row count like any other update query.

The second solution is used when table N1a and N2 are in different databases. In this case, you must run separate SQL states in the two databases, select from one and update (insert) to the other, usually in a jython script. The latter can be one operation if you DB supports multiple rows in the VALUES clause.

Something like this (jython):

# Obtain rows of data for columns A through D
ds = system.db.runNamedQuery("get_from_N1a_for_copy", {"column_e_param": someValue})

# Named queries do not return PyDatasets--make one for convenience looping
pyds = system.dataset.toPyDataSet(ds)
for row in pyds:
    params = {}
    for c in ("column_a", "column_b", "column_c", "column_d"):
        params[c] = row[c]
    params["column_e"] = someValue
    system.db.runNamedQuery("add_one_row_to_n2", params)

If your database can run multi-row inserts, and you turn on “Legacy Queries” permission in your project properties, you can do something like this instead:

# Obtain rows of data for columns A through D
ds = system.db.runNamedQuery("get_from_N1a_for_copy", {"column_e_param": someValue})

# Named queries do not return PyDatasets--make one for convenience looping
pyds = system.dataset.toPyDataSet(ds)
updateSQL = "INSERT INTO n2 (column_a, column_b, column_c, column_d, column_e VALUES "
columnData = []
for row in pyds:
    # Make a long single-dimensional list of all row data suitable for runPrepUpdate
    columnData += [row[c] for c in ("column_a", "column_b", "column_c", "column_d")]
    columnData.append(someValue)

# Add the correct number of row-values placeholders
updateSQL += ",\n  ".join(["(?, ?, ?, ?, ?)"] * len(pyds))

system.db.runPrepUpdate(updateSQL, columnData, "DBnameForN2")
1 Like

Hi,
Very helpfull your answer. i will test you sugestion ASAP.
Anyway, I am disappointed that there is no more intuitive way to transfer data from one table to another in Ignition.
I have worked with other IDEs and this particular “function” is more intuitive to apply.
Howewer, all solutions are good.:slight_smile:
I will try tomorrow morning, right now in Europe is midnigth.
Thanks again,

Heh, I suspected so. I noticed a bit of «franglais» in your post. No worries.

Hi again,
Here is what I did:
In my bouton:
image

In my Named Query:

But I have this error:

Looking in the internet: I found this:

So, I’ve made the correction (as said in the post import “java.lang.Exception”) but sill no working. Maybe is a size array problem? Do you know this kind of issues?

Thanks

From line 2, it means your selected row is not within 0 to rows-1.

Also, on line 5, you have id in quotes as a dictionary key, when it only makes sense as the value. The dictionary key must be the name of the query parameter you are supplying. (Why would you use the parameter name column_e_param? That was just a made-up name to go with an imaginary column_e.)

Hi,
Sorry for the late reponse. It has been a week full of events… I have made some modifications to your proposal (which is valid). Here is what I have done:

On the button:

In the Named Query: (update)

I did not understand your latest proposal. However, it allowed me to understand some things.

I appreciate your suggestions. Thanks again.