# Copy from one SQL table to another

**URL:** <https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862>\
**Category:** Ignition\
**Tags:** database, ignition79\
**Created:** [November 14, 2018, 6:12pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862 "2018-11-14T18:12:32Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\
**Post date:** [November 14, 2018, 6:12pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/1 "2018-11-14T18:12:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 14, 2018, 6:19pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/2 "2018-11-14T18:19:50Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\
**Post date:** [November 14, 2018, 6:26pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/3 "2018-11-14T18:26:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 14, 2018, 6:30pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/4 "2018-11-14T18:30:01Z")

</div>

> [@bellm](#):
>
> the AS keyword lets me alias the ? to HangerNumber

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.

---

<div class="post-metadata">

**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\
**Post date:** [November 14, 2018, 6:43pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/5 "2018-11-14T18:43:03Z")

</div>

> [@pturmel](#):
>
> The list of columns to INSERT just has to be 1:1 with the expressions in the SELECT.

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.

---

<div class="post-metadata">

**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\
**Post date:** [November 14, 2018, 7:05pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/6 "2018-11-14T19:05:36Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 14, 2018, 7:11pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/7 "2018-11-14T19:11:07Z")

</div>

> [@bellm](#):
>
> I am sure it is something stupid I just cant see it

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

> [@bellm](#):
>
> On a side note, is there a way to format the query part so it is easier to read?

I usually place the sql into a script module constant using [triple-quoted multiline strings](https://docs.python.org/release/2.5.3/ref/strings.html).

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 3, 2020, 10:30am UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/8 "2020-06-03T10:30:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 3, 2020, 12:28pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/9 "2020-06-03T12:28:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 6, 2020, 9:08pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/10 "2020-06-06T21:08:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 6, 2020, 9:11pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/11 "2020-06-06T21:11:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 6, 2020, 9:33pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/12 "2020-06-06T21:33:54Z")

</div>

Thanks,  
But I want to clarify my problem:

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3b8040e1a65c454ec5be88a29b796e242d35c541.png)

Here is my Database:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/aa1e9e54f505fb9b3366220a705cabc68a0e9bf4.png)

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).

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/a24b1b8b151449ab819719320c1221ddce1bab81.png)  
I have made this little script however it doesn’t work:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/7661fec096c638ebc666eb3538f7418a6b01821e.png)

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 7, 2020, 9:02pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/13 "2020-06-07T21:02:32Z")

</div>

> [@Icom](#):
>
> But I want to clarify my problem:

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:

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

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

```auto
# 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:

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

```

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 7, 2020, 10:00pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/14 "2020-06-07T22:00:51Z")

</div>

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.🙂  
I will try tomorrow morning, right now in Europe is midnigth.  
Thanks again,

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 7, 2020, 10:05pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/15 "2020-06-07T22:05:10Z")

</div>

> [@Icom](#):
>
> I will try tomorrow morning, right now in Europe is midnigth.

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

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 7, 2020, 10:58pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/16 "2020-06-07T22:58:52Z")

</div>

Hi again,  
Here is what I did:  
In my bouton:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/9bb2aad62b269d12f5f1a4ecdf404fa059903dde.png)

In my Named Query:

But I have this error:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4a14b483d86f911d42c5f91ed8473a54b2793cd7.png)

Looking in the internet: I found this:

> [@Error Handling - ArrayIndexOutOfBoundsException](http://forum.inductiveautomation.com/t/error-handling-arrayindexoutofboundsexception/20011):
>
> I have a situation where I need to handle an exception due to an ArrayIndexOutOfBoundsException. In the manual, it says that this can be called out for an exception-specific failover. However, another error is thrown saying that ‘ArrayIndexOutOfBoundsException’ is not defined. Using the example code from the manual, this is the code I’m trying to use. myDataset = system.dataset.toDataSet([“colName”], [[0]]) try: print myDataset.getValueAt(0,5) except ArrayIndexOutOfBoundsException: print “…

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 7, 2020, 11:49pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/17 "2020-06-07T23:49:26Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![Icom](https://avatars.discourse-cdn.com/v4/letter/i/e68b1a/32.png) [@Icom](https://forum.inductiveautomation.com/u/Icom)\
**Post date:** [June 12, 2020, 2:34pm UTC](https://forum.inductiveautomation.com/t/copy-from-one-sql-table-to-another/20862/18 "2020-06-12T14:34:10Z")

</div>

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:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/ea2176ffa836b1af39b610fb935155729d4f0807.png)

In the Named Query: (update)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/8/859c372f5234307a0299cbf6a6dc2c953efea08d.png)

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

I appreciate your suggestions. Thanks again.