# Copy a record via a push button **URL:** \ **Category:** Ignition\ **Created:** [September 17, 2018, 3:27pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689 "2018-09-17T15:27:02Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 17, 2018, 3:27pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/1 "2018-09-17T15:27:02Z")
I have a recipe table set up which services several machines. I have a transaction that is triggered by the PLC and uses a where clause for the part number to do a DB-\>OPC transfer. Everything works there, no problem. the problem starts now because up until recently parts were dedicated to certain machines. Now we are allowing multiple machines to run a part number which is fine except each recipe needs to be “tweaked” for the individual machine. To do this we added a machine number column that uniquely identifies that part number record for each machine. Now I have a lot of recipes that need to be duplicated both now as well as in the future so I wanted to be able to copy a complete recipe (record) and update the machine number. So essentially duplicate the entire recipe row with the exception of an updated machine number column. Is there an easy way to do this other than the component button scripting on mouse action clicked that runs the system.db.runPrepUpdate for all of the 120 individual recipe columns? it would seem that if I can already query this into a table or custom dataset and already have all my data formatted I should be able to more easily create a new record. without the mind-numbing typing. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 17, 2018, 6:12pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/2 "2018-09-17T18:12:50Z")
I’m not sure I follow, is the mind-numbing typing the writing script for the prep update on button push? [EDIT] Okay I think I see where you are going with this. writing an update query for all 120 rows is a lot to type. You can use a query to do this. ```auto insert into recipes select*from recipes where id = ? ``` this will copy all the columns and generate a different primary key using MySQL. what db are you using? ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 17, 2018, 6:33pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/3 "2018-09-17T18:33:33Z")
Yes, it is at least partially that, and also the updates that have to then happen any time a column is added to every place that they might want to add/copy a recipe. Add to that the awful readability of the line where I assign the 120 fields for the update/insert statement. I was just hoping for a quicker easier way since I can query the existing recipe and already have all the data in a dataset that matched the table perfectly. ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 17, 2018, 6:36pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/4 "2018-09-17T18:36:15Z")
I am using MSSQL located on a remote server and not administered by me (nor do I have rights) so everything I do must go through ignition to be able to have the rights to do anything other than read. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 17, 2018, 7:24pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/5 "2018-09-17T19:24:42Z")
This doesn’t seem to have an easy answer that I can think of off the top of my head. You can ```auto system.db.runNamedQuery('CreateTempTable',) system.db.runNamedQuery('UpdateTempTable',) system.db.runNamedQuery('InsertTempTable',) system.db.runNamedQuery('DropTempTable',) ``` So basically you create a temporary table based on your recipe table, update the columns you want, then insert this into the recipe table, then delete the temporary table. ```auto CREATE TABLE temp_table as SELECT*FROM recipes UPDATE temp_table set machine_id = 3 UPDATE temp_table set primary_key = null INSERT INTO recipes SELECT*FROM temp_table DROP TABLE temp_table ``` Maybe this can help you? ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 17, 2018, 8:13pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/6 "2018-09-17T20:13:48Z")
It may help, at least in that There seems to be no obvious easier way. As for the creating the temporary table and such, I can follow the [quote=“dkhayes117, post:5, topic:19689”] CREATE TABLE temp\_table as SELECT_FROM recipes UPDATE temp\_table set machine\_id = 3 UPDATE temp\_table set primary\_key = null INSERT INTO recipes SELECT_FROM temp\_table DROP TABLE temp\_table [/quote] However I have no idea how to implement this into the button to make it happen, I also have never used named queries. I am a database kindergartner and an Ignition 3rd grader. I am just now learning not to eat paste. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 17, 2018, 8:16pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/7 "2018-09-17T20:16:04Z")
Some older version of Ignition do not have named queries, I think it was introduced in 7.9.4. What version do you have? ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 18, 2018, 6:50pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/8 "2018-09-18T18:50:02Z")
I am currently using 7.9.4 and yes, I do have named queries on my tree, I just haven’t used them yet. ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 19, 2018, 11:09am UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/9 "2018-09-19T11:09:15Z")
Okay, so how about this, is there a way to store a pydataset directly to a table? I have all of my queried data in a pydataset and can move it down to a table one cell at time, could it be pushed out as one dataset as an insert somehow? ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 19, 2018, 12:50pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/10 "2018-09-19T12:50:21Z")
> [@ssilk76](#): > > could it be pushed out as one dataset as an insert Not that I know of, the dataset has to be iterated through on an insert. When you bind a table to a query, the table data from the query is already a dataset. Moving it as a pydataset to another table doesn't really change anything. You may look into setting up a script to export the data as a csv, then import that into the db. I've never done this, and I don't use MSSQL so you may have to research the method to do this. csv import and the very repetitive runNamedQuery method is all I can think of. There maybe a better solution that someone else can come up with? ---
**Author:** ![ssilk76](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@ssilk76](https://forum.inductiveautomation.com/u/ssilk76)\ **Post date:** [September 21, 2018, 8:56pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/11 "2018-09-21T20:56:31Z")
Okay, so I have almost everything working on this little project. My last hurdle is in relation to the pydataset values that are returned from NVARCHAR’s queried in the SQL data. When I go to write the variable back into a table using reprepupdate I get an error which I believe the most important part is the text “The conversion from UNKNOWN to UNKNOWN is unsupported” and I assume this is because of a type mismatch between the pydataset and sql. They pydataset value is: u’Kalcor 709’ while the result from an sql query just returns just the text without the u’XXXXXXX’ I have searched the forums and I have seen some information regarding this, but now how to change or coerce this data into a format acceptable to sql. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 21, 2018, 9:23pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/12 "2018-09-21T21:23:55Z")
Without seeing anything else, you can convert the value to a string - str(value). Then replace the u’ and ’ with replace() if needed ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 24, 2018, 3:22pm UTC](https://forum.inductiveautomation.com/t/copy-a-record-via-a-push-button/19689/13 "2018-09-24T15:22:41Z")
Post your code. `u'XXXXX'` is just the Python [`repr()`](https://docs.python.org/2/library/functions.html#repr) output for a unicode string - which is exactly what an NVARCHAR column is. You’re probably still a layer inside the PyDataset (eg, you’re trying to insert a PyRow) rather than directly inserting a string.