Better SQL Insert Syntax

The SQL Insert statement is used when you want to insert new rows of data into the existing tables.

For example, if a table has fields A, B, C and D four columns. Then you can use:

insert into table (`A`, `B`, `C`, `D`) values ('a', 'b', 'c', 'd')

Or, even simpler without explicitly giving the column names:

insert into table values ('a', 'b', 'c', 'd')

This requires you know the order (the default order) of the columns otherwise it won’t work, and thus this should be avoided at all costs.

The best syntax for insert statement (MySQL syntax extension) should be:

insert into table
set `A` = 'a', `B` = 'b', `C` = 'c'

In this way, you use similar syntax as update statement. Why this is better? I give you one example,

1
2

#define INSERT_QUERY "insert into TABLE values ('%s','%s',now(),'%d-%d-%d','%s','%s','%s','%s','%s',%d,%d,'%s',"\
        "'%0.2X-%0.2X-%0.2X-%0.2X-%0.2X-%0.2X','%s',1,0)"

#define INSERT_QUERY "insert into TABLE values ('%s','%s',now(),'%d-%d-%d','%s','%s','%s','%s','%s',%d,%d,'%s',"\
		"'%0.2X-%0.2X-%0.2X-%0.2X-%0.2X-%0.2X','%s',1,0)"

A legacy C++ code has stored the insert query however, this limits the future changes to the table structure as adding or deleting columns (even rearranging the order) will make the SQL query _NOT_ working. But for the insert set statement, this works if the columns you are dealing with still exist in other words, adding extra columns won’t affect the SQL query at all.

–EOF (The Ultimate Computing & Technology Blog) —