[SOLVED] bash cut and paste SQL insert statement

Issue

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.

Solution

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
);

Answered By - Ed Morton