Generate INSERT or UPDATE or DELETE commands from results

[hben-align]

I would like to suggest a new feature that can be added to the copy options.

In the current version, you can copy the result set with or without the headers, the new feature will enable you to copy the data in a format of INSERT or UPDATE or DELETE.

for example:

1. table structure: my_table (id int primary key, name text, updatedate timestamp, age int)
2. query "select * from my_table"
3. the result will be: id (integer) | name (text) | updatedate (timestamp) | age (int) 1 | john | 2024-09-07 00:00:01.000| 18 2 | tra | 2024-09-07 00:00:01.000| 36 3 | volta | 2024-09-07 00:00:01.000| 72

there will be 1 more option in the "Copy options": 'Custom'. when choosing custom, a dialog will be opened. in that dialog you can select which dml action you want, and some configurations for each DML option.

the DML options:

• Copy as INSERT will copy like so: INSERT INTO public.my_table (id, name, updatedate, age) VALUES (1, 'john', '2024-09-07 00:00:01.000', 18); INSERT INTO public.my_table (id, name, updatedate, age) VALUES (2, 'tra', '2024-09-07 00:00:01.000', 36); INSERT INTO public.my_table (id, name, updatedate, age) VALUES (3, 'volta', '2024-09-07 00:00:01.000', 72);

• Copy as UPDATE will copy like so: UPDATE public.my_table SET name = 'john', updatedate = '2024-09-07 00:00:01.000', age = 18 WHERE id = 1; UPDATE public.my_table SET name = 'tra', updatedate = '2024-09-07 00:00:01.000', age = 36 WHERE id = 2; UPDATE public.my_table SET name = 'volta', updatedate = '2024-09-07 00:00:01.000', age = 72 WHERE id = 3;

• Copy as DELETE will copy like so: DELETE FROM public.my_table WHERE id = 1; DELETE FROM public.my_table WHERE id = 2; DELETE FROM public.my_table WHERE id = 3;

  ---

  it should work based on the table structure, so for example UPDATE and DELETE condition will be based on Primary Key. the actual values can be treated based on the column data type which already presented in the result set, so text, dates and so on will be encapsulated in quotes ('like so'), integer wont be encapsulated, special data types will just be used in cast like so: 'data'::custom_dt.

Drawbacks:

1. Complex query with more than one table.
2. No primary key or any unique column in result set or in table
3. Incremental id or generated uuid pk column
4. TBD...

Solutions:

1. Open dialog where a table name can be set
2. Open dialog where a column can be set as the "primary key" in the condition it can result bad queries (for example if I only query name and age and I set WHERE condition to be 'name' and there are duplicates, UPDATE commands will update multiple records, but its by design and its the responsibility of the person that works on DB.
3. checkbox for including pk in INSERT commands.

[khushboovashi]

@hben-align, Copy As Insert makes sense, but what will the use of Delete and Update be? I can easily delete rows from the Result set, and the user needs to change the values for an update. In which scenarios do you find it helpful? Also, we already have a feature to generate INSERT/CREATE/UPDATE/DELETE/SELECT scripts.

[hben-align]

@khushboovashi Assuming you have only one database to work on, you mostly don't need it because you can insert update and delete based on queries. (there are some cases that it will be helpful)

but when you have multiple databases (or even technologies) it can be useful to have this kind of thing. for example: I have a database with AWS DMS task that replicates data to another database. we had an issue with the DMS and we needed to "close the gap" in a specific time frame, meaning that we needed to insert all records that hasn't inserted to the target DB, and update records that hasn't updated on the target.

another use case is to migrate specific data between databases easily: query source DB -> copy resultset as INSERT -> insert into target

actual use case I encountered: I had a DB with team_id and item_id (1:many), I had another DB with department_id and a hash tables with team_id:department_id and I had to insert into the second database department_id: item_id.

I did it using python, but with a simple query (using CTE) I could have had generated INSERT statements for the second database and finish with it without code development and testing and finish this task in 10 minutes top.

or for example, in local use, you want to test some data changes and save the last state: so you can copy as UPDATE (on PK). make the update, and for rollback you can use the UPDATE statement (instead for example store the data in a temp table)

or if you have a configuration table, and you changed values in dev DB and you want to create a version script for staging based on those changes, you can create the UPDATE scripts based on a simple select (instead of creating it manually)

so its mostly used when you have a lot of databases and some times duplicated data (very acceptable in micro-services environment) and you need to perform DML operations based on other databases.

and regarding generate scripts, is it based on a specific result set? unless I'm missing something, it isn't... as well for UPDATE or DELETE statements...