SQL Insert Syntax
December 16, 2016
When I’m not writing exercises for Programming Praxis, my day job has me writing SQL and PL/SQL code for a large Oracle database. One task I frequently perform is inserting records into a table, using the following syntax:
insert into tablename ( field_1, field_2, field_3, ...) values ( value_1, value_2, value_3, ...)
tablename is replaced by the name of the database table into which records are being inserted, each
field is the name of a field in the table, and each
value is the value to be inserted. The correspondence between field name and value is positional.
I can’t tell you how many times over the years I wrote that wrong. When I inadvertently skip a field, an error message politely tells me of my mistake. But when I transpose two fields, both of the same type, there is no error message, and I happily go on my way with bad data in the database.
I finally decided to do something about the situation; I wrote a program that converts the syntax shown below, which makes it easy to see the correspondence between fields and values, to the Oracle syntax shown above:
insert into tablename ( field_1 => value_1, field_2 => value_2, field_3 => value_3, ...)
That’s much better: Easy to get right, much harder to get wrong. A significant payback on a few minutes of effort. Why didn’t I do this years ago!?
Your task is to find something annoying in your programming environment, and fix it; you can borrow my annoyance in the unlikely event you have none of your own. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.
Pages: 1 2