Bash Cut And Paste SQL Insert Statement

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 f

Solution 1:

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

Solution 2:

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