Copy and paste recipe saved in database via script

Ignition
, ,
1

Good morning,
I'm trying to look for the best solution for this problem.

I have a table in a sql server database and I would like create a button "Save as" where I select a already created recipe name and I choose a new name to save so to have a copy of an oldrecord with a different name.

I guess I could do something like this, but I wonder how I can change the recipe name

insert into recipes
select*from recipes
where RecipeName = selectedRecipeName

Should I make an update after this?

2

It might help to tag your question as Vision or Perspective.

Your title doesn't match the question. This is not a "copy and paste" question.

3 
insert into recipes
select 'new_name', foo, bar, pox, wuz
from recipes
where RecipeName = selectedRecipeName

I don't think perspective/vision is relevant here, but it should probably be tagged 'database' or whatever is the closest in the tags list

4

Pascal, does the insert work when you haven't specified the field names?

5

Yep, at least with mssql. If you're inserting "everything", you don't need to specify what you're inserting.
Won't work with a select * though as you'd also be pulling the id.
Gotta be careful with the order though. It may be better to make the columns explicit...

insert into recipes (RecipeName, foo, bar, pox, wuz)
select 'new_name', foo, bar, pox, wuz
from recipes
where RecipeName = selectedRecipeName
2 Likes
6

@Transistor, I'm sorry for the title, the copy and paste is my goal, but if is better to change it to be more clear please tell me how to change. I change the tag as you suggest.

@pascal.fragnoud, I would prefer to make a Select * because I have hundred of columns and happen that I need to add new ones, so I should insert them manually.

7

I'm testing it with this script with no success


RecipeName = system.tag.readBlocking("path")[0].value

query = 	"""
			INSERT INTO Recipe_A4 
			SELECT * 
			FROM Recipe_A4 
			WHERE RecipeName = ?
			"""
					
args = ["%s" % RecipeName ]

ds = system.db.runPrepUpdate(query, args)

but I have this error (I translate it because the error in not in english), I hope the translation is correct

You can specify an explicit value for the identity column in the table 'Recipe_A4 ' only when using a list of columns and IDENTITY_INSERT has a value of ON

8

It means you're trying to manually set the value of an automatic column. You can't do that, and that's why you can't use select *.

If you don't want to manually type everything, I guess you could do a select * from your table in the script console, print the column names, copy and paste them, and remove the id column...

4 Likes
9

Thank you for the help. At this point I will do as you suggest.

I exctract the column names in the script console with this

ds = system.db.runPrepQuery(query, args)	
rsDataset = system.dataset.toPyDataSet(ds) 

print rsDataset.getColumnNames()

and I have the results in one single row like this

[ricette_a4_ndx, Col1, Col2, ecc]

How could I have the results row by row like this

ricette_a4_ndx, 
Col1, 
Col2, 
ecc

just to be faster with the copy of the names

Thank you

10 
print '\n'.join(column_names)

or

for col in column_names:
    print col
1 Like
11

Thank you very much, have a nice day.