SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Save As Script - new features

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Save As Script - new features
Author Message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Save As Script - new features Reply with quote
Hello
I would like to rise a feature request, extending Save As Script function by:
- ability to store script into clipboard
- ability to select one of possible method: INSERTs, UPDATEs, MERGE (INSERT ON CONFLICT)

Heidi SQL has this part implemented more then perfectly.
Mon Jul 16, 2018 6:50 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Can you please elaborate a bit more on the second point?

Which specific Save As Script are you referring to?
A brief example of how to save and how to save it using INSERTs, UPDATEs, MERGE (INSERT ON CONFLICT) would be very helpful.

Sorry I'm not familiar with Heidi SQL, don't know how it's implemented in that tool.
Mon Jul 16, 2018 8:09 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Here is example screenshot of mentioned feature. of course, it works with data collected in datagrid by selecting them prior to choosing this feature.



As you can see, you can export grid content to file as well as store it to clipboard, making possible to paste it anywhere quickly. Current version of SA offers saving to file only.
Then Heidi offers export to various formats. As you can see it can export data to several formats. One of them are SQL INSERTs and SQL REPLACEs. Currently SA can export only INSERTs
Of course I'm not asking to copy all possible features of other software, but found SA is missing ability to create updates/merges in quick way.

Simply SA could generate following constructs from datagrid:

INSERT INTO table (field list) VALUES (value list)

UPDATE table SET
field = value list
WHERE pk = pkvalue

INSERT INTO table (field list) VALUES (value list)
ON CONFLICT (pk) UPDATE SET
field = EXCLUDED.value list

or even optionally

INSERT INTO table (field list) VALUES (value list)
ON CONFLICT (pk) DO NOTHING
Mon Jul 16, 2018 8:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Thank you for the details. Very helpful. I will pass them alone

Couple of tips you may find useful.
There is an option in the Script Table Data dialog to "Output script to new code pane" so that a file is not required, it's optional. You don't even need to paste it and you can script data in multiple tables at once.
The encoding is always utf-8, but if you want to convert it to something else, you can use File menu in the SQL Editor and then File Encoding, Character Set and File Format options to convert it to something else.
To generate scripts with ON CONFLICT clause, one can use either scriptable Code Snippets with macros feature or the Code Generator feature and develop a snippet or template to generate the required scripts, then use it instead of the Save as Script. Please refer to the existing templates as examples of how that can be done. I think that should be a fairly trivial task using a simple template like (WARNING , I haven't tried it, just sharing an idea)

$$
SELECT
'INSERT INTO "$OBJECT(ins_schema, table)$"."$OBJECT(ins_object, table)$"
( $COLUMNS$)
VALUES (' || string_agg('''' || $COLUMNS$::TEXT || '''', ',') || ')
ON CONFLICT ON CONSTRAINT ' || (SELECT constraint_name FROM information_schema.table_constraints WHERE TABLE_NAME = '$OBJECT(table)$') || ' DO NOTHING;'
FROM $OBJECT(table)$
$$

The output of this template would be a bunch of INSERT statements with ON CONFLICT clause referring to table's primary key constraint name and DO NOTHING action.

Different templates like that can be developed for different scenarios


Last edited by SysOp on Mon Jul 16, 2018 10:08 am; edited 1 time in total
Mon Jul 16, 2018 9:57 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Thank you for the idea.
Never went for scriptable code snippets. It looks to be worth to try.

with regards
Mon Jul 16, 2018 10:06 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.