bash cut and paste SQL insert statement

Question

I would like to remove a column name value pair from one INSERT statement and move it into another INSERT statement. I have about a hundred seperate files that have this sort of format (although the format may vary slightly from file to file, for instance some users may have put the entire INSERT statement on one line).

INPUT

INSERT INTO table1 (
    col1,
    col2
)
VALUES (
    foo,
    bar
);

INSERT INTO table2 (
    col3,
    col4_move_this_one,
    col5
)
VALUES (
    john,
    doe_move_this_value,
    doe
);

OUTPUT

INSERT INTO table1 (
    col1,
    col4_move_this_one,
    col2
)
VALUES (
    foo,
    doe_move_this_value,
    bar
);

INSERT INTO table2 (
    col3,
    col5
)
VALUES (
    john,
    doe
);

In general with the above format I was thinking I could use sed and cat in a script to find line numbers of each line to be moved and then move it, something like this.

for file in *; do
    line_number=$(cat -n ${file} | sed some_statement | awk to_get_line_number)
    # etc...
done

...but maybe you guys can recommend a more clever way that would work also if the INSERT statement is on one line.

Answer 1

A GAWK version that relies on gensub's backreference feature and heavily on regex.

$ cat > test.awk
BEGIN {
    RS=" *) *; *"         # set RS to ");" and prepare to space as well
    ORS=");\n"
}

{
    sub(/^[ \n]*/,"")     # remove emptiness before second INSERT
}

$0 ~ /^INSERT/ && NR==1 { 
    a=$0                  # store the first INSERT
}

$0 ~ /^INSERT/ && NR==2 { # store the second and use gensub to 
    b=$0                  # find the second variables in INSERT and VALUES
    split(gensub(/(INSERT|VALUES)[^\(]*\(([ \n]*[^,]*,){1}[ \n]*([^,]*)[^\)]*\)*[ \n]*/,"\\3 ","g"),c," ")
}

END {                     # print first INSERT with second variables in place
                          # and second INSERT with variables removed
    print gensub(/((INSERT|VALUES)[^\(]*\((([ \n]*)[^,]*,){1})/,"\\1\\4"c[++i]",\\5","g",a)
    print gensub(/((INSERT|VALUES)[^\(]*\(([ \n]*[^,]*,){1})[ \n]*[^,]*,/,"\\1 ","g",b)
}

This solution assumes that variables to copy are the second variables in the second INSERT after keywords INSERT and VALUES and that they are added to those same places in the first INSERT. Solution is space and \n friendly but doesn't support \t, easily fixed I assume.

$ awk -f test.awk file
INSERT INTO table1 (
    col1,
    col4_move_this_one,
    col2
)
VALUES (
    foo,
    col4_move_this_one,
    bar
);
INSERT INTO table2 (
    col3, 
    col5
)
VALUES (
    john, 
    doe
);

Answer 2

With GNU awk for true multi-dimensional arrays, 3rd arg to match(), multi-char RS and \s/\S syntactic sugar:

$ cat tst.awk
BEGIN { RS="\\s*);\\s*" }
match($0,/(\S+\s+){2}([^(]+)[(]([^)]+)[)][^(]+[(]([^)]+)/,a) {
    for (i in a) {
        gsub(/^\s*|\s*$/,"",a[i])
        gsub(/\s*\n\s*/,"",a[i])
    }
    tables[NR] = a[2]
    names[NR][1]; split(a[3],names[NR],/,/)
    values[NR][1]; split(a[4],values[NR],/,/)
}
END {
    names[1][3] = names[1][2]
    names[1][2] = names[2][2]
    names[2][2] = names[2][3]
    delete names[2][3]

    values[1][3] = values[1][2]
    values[1][2] = values[2][2]
    values[2][2] = values[2][3]
    delete values[2][3]

    for (tableNr=1; tableNr<=NR; tableNr++) {
        printf "INSERT INTO %s (\n", tables[tableNr]
        cnt = length(names[tableNr])
        for (nr=1; nr<=cnt; nr++) {
            print "    " names[tableNr][nr] (nr<cnt ? "," : "")
        }
        print ")"

        print "VALUES ("
        cnt = length(values[tableNr])
        for (nr=1; nr<=cnt; nr++) {
            print "    " values[tableNr][nr] (nr<cnt ? "," : "")
        }
        print ");\n"
    }
}

.

$ awk -f tst.awk file
INSERT INTO table1 (
    col1,
    col4_move_this_one,
    col2
)
VALUES (
    foo,
    doe_move_this_value,
    bar
);

INSERT INTO table2 (
    col3,
    col5
)
VALUES (
    john,
    doe
);