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,
    ...)

Here, 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