SQL Insert Syntax

December 16, 2016

Here’s the program I wrote:

#! /usr/local/bin/gawk -f

/insert into/ {
    sub(/^ *insert into */,"")
    sub(/ .*/,"")
    print "insert into", $0, "(" }

/=>/ {
    sub(/^ */,""); sub(/ *[,)] *$/,"")
    split($0, fv, " *=> *")
    f[++n] = fv[1]; v[n] = fv[2] }

    for (i=1; i<=n; i++)
        printf "%s%s\n", f[i],
            (i == n) ? ")" : ","
    print "values ("
    for (i=1; i<=n; i++)
        printf "%s%s\n", v[i],
            (i == n) ? ")" : "," }

I saved the program in insert.awk, then did chmod +x, and ran it like this:

$ insert.awk < insert into tablename (
>     field_1 => value_1,
>     field_2 => value_2,
>     field_3 => value_3)
insert into tablename (
values (

There is no indentation of fields and values, but it is valid Oracle syntax, which is the point of the exercise. Many improvements are possible (leave the original in comments, extract the input from comments so multiple iterations are simple, provide indentation of fields and values, process insert statements within a larger body of code, maintain existing indentation within a larger body of code, and more), but I’m satisfied with what I wrote, and may never get around to improving it. One nice feature of the program is that I can call it within vi by putting the cursor on the i of insert in command mode and issuing the !}insert.awk command.

The larger lesson is that, even if your programming environment is, um, annoying (that’s not the word I normally use in this situation), you are responsible for your own productivity, and you should build small tools like this to fix the worst parts of it.

You can run the program at http://ideone.com/JIO84m.


Pages: 1 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: