MariaDB :Producing an insert from another table with concat

Question

I want to produce an insert out of a table for another table with concat. All works well, besides a little devil ... At the end of the concat I get an ' after the ; Without the ' it works all well.

How may I get rid of the ' ?

Sample:

drop table if exists gf;create table gf(id int auto_increment primary key,datum date,time time,vornum int,name varchar(40),surname varchar(40),open varchar(40),krz varchar(5),knd varchar(5),titel varchar(40)unique,anfrage text,form varchar(12));
    drop table if exists knd;create table knd(id int auto_increment primary key,datum date,time time,vornum int,name varchar(40),surname varchar(40),open varchar(40),krz varchar(5),knd varchar(5),titel varchar(40)unique,anfrage text,form varchar(12));
    
    insert into knd values('0',current_date,current_time,'0','Myname','Myfirstname','MyOpen','MyKrz','MyKnd','MyWish','MyWish','MyForm');

     select CONCAT('insert into gf values(','''0'',''',datum,''',''',time,''',''',vornum,''',''',name,''',''',surname,''',''',open,''',''',krz,''',''',knd,''',''',titel,''',''',anfrage,''',''',form,''');''')from
    knd where id =1;

Result is:

insert into gf values('0','2025-05-17','23:02:03','0','Myname','Mylastname','MyOpen','MyKrz','MyKnd','MyWish','MyWish','MyForm');' (<little devil here)

@progman Well, that's a bit of complicated. If I have a table with an "auto_increment primary key",any false insert would increment the id, without affecting any rows. This leads to gaps in the id. (1,4,5,8) instead of (1,2,3,4), because MariaDB and Mysql allocate the id before testing the insert. I want to solve this problem. So, I defined a "test- table", where inserts are tested and proved. Then, I want to take the successfully inserted data from that "test-table" into the "real-table". By using this workaround, I get the "id" in the "real-table" consecutive. — MBE
– MBE, Commented May 17 at 22:40

Guillaume Outters · Accepted Answer · 2025-05-18 07:02:02Z

3

A literal answer

You inadvertently used a triple ''' at the end while you needed a single one:
,form,''');''')from
should be:
,form,''');')from

(see both queries in a fiddle)

Going further

… But using this kind of casting to string, on not always string columns (like times) and without escaping is dangerous:
loss of precision, and more importantly risk of SQL injection or at least broken workflow if one of your fields contains a ', like in My wish it's to break this database.

You can directly insert from a select, like running this query when your knd row has been validated:

insert into gf
select 0, datum,time,vornum,name,surname,open,krz,knd,titel,anfrage,form
from knd where id =1

(of course, contrary to the "textual insert", this requires that both gf and knd are in the same database; and that you keep rows in knd until they've been copied to gf)

And if the validation is not waiting only for the user, but for other conditions too (verifying that the name is not empty, that the form still exists in some reference table), you can of course add those conditions to the where:

…
from knd where id =1 and length(name) >= 3 and …;

Thus the validation occurs in the same database transaction as the insert avoiding inconsistencies that could occur due to data changing between your validation and your insert.

edited May 18 at 7:02

answered May 18 at 6:30

Guillaume Outters

7,4961 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MBE May 18 at 21:30

Thanks for this useful answer and comments. I like to keep the inserts "textual", because I can produce an input history in this way, as a table 'concats', gathering the inserts from various tables, even from various databases. Please improve, if you have an idea.

ysth May 18 at 21:59

@MBE there are better ways to keep history, but that should be raised as a new question. insert...select is the correct answer here.

MBE May 18 at 23:01

@ysth I think, this is part of my question.

Guillaume Outters May 18 at 23:29

@MBE, "What's the best way to …" would be opinion based; but you could try to explore having a table with timestamps (infinitely more searchable than a bunch of text files), filled by insert select from knd, with a storage engine targeting persistence over performance or even to an external dedicated DB. If files is good enough for you, prefer dumps with the RDBMS' tool or use CSV to get a compact & robust representation.

Collectives™ on Stack Overflow

MariaDB :Producing an insert from another table with concat

1 Answer 1

A literal answer

Going further

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

A literal answer

Going further

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related