Generate SQL UPDATE from Excel CSV file I must write updates in a database from a flat file (CSV).  I want to do that in the shell, with tools such as AWK.

#!/bin/bash

cat in.csv | sed -e '1d' | awk -F';' -v q=\' '{               # For each line.
    print "DECLARE @v_trmID varchar(16) = " q $1 q
    print "DECLARE @v_trmNom varchar(6) = " q $3 q
    print "DECLARE @v_trmNbrTrav smallint = " $4
    print "IF EXISTS (SELECT 1 FROM trimestre WHERE trmID = @v_trmID AND trmNom = @v_trmNom)"
    print "    BEGIN"
    print "        UPDATE trimestre"
    print "        SET trmNbrTrav = @v_trmNbrTrav"
    print "        WHERE trmID = @v_trmID AND trmNom = @v_trmNom"
    print "    END"
    print "ELSE"
    print "    BEGIN"
    print "        PRINT " q "The script execution FAILED for record " NR " (pfiID " q " + @v_trmID + " q ", trimestre " q " + @v_trmNom + " q ")." q
    print "    END"
    print "go"
    print ""
}'


Though, there a 2 things I don't like:


The way quotes are inserted; it becomes really difficult to follow, even if I choose for the simpler way I found to write quotes inside an AWK string (instead of multiple escape sequences).  Still, not that readable.
The fact that every SQL line is not readable as is.  Code is not highlighted as SQL.  I'd like to find a "here doc" solution, where I wouldn't have to prefix lines with printf.


Do you have any pieces of advice or better ideas on the way to write robust (because more readable / easily modifiable) code?
You can turn this into "here doc" solution by using wrapping it in a process substitution &lt;(...), like this:

awk -f &lt;(cat &lt;&lt; "EOF"
{
    ... // awk script, as if in a file
}
EOF
)


This way, you can write ' directly, as if in a .awk script file,
because there ' is no longer an enclosing character.

Note that cat &lt;&lt; "EOF" is necessary instead of simply cat &lt;&lt; EOF to avoid variable expansion of $1, $3 and $4.

Also, to avoid multiple print statements,
you will have to embed newlines with \n, and end lines with \ to continue the same print statement on the next line.

Finally, you don't need the sed -e 1d, because awk can do this alone, using a NR &gt; 1 filter. However, to account for the extra line in the input, whenever you use NR in the awk script, you would have to change that to NR - 1.

Putting the above together, this script is equivalent to yours (produces same output):

awk -F';' -f &lt;(cat &lt;&lt; "EOF"
NR &gt; 1 {
print "\
DECLARE @v_trmPfiID_fk varchar(16) = '"$1"'\n\
DECLARE @v_trmNom varchar(6) = '"$3"'\n\
DECLARE @v_trmNbrTrav smallint = "$4"\n\
IF EXISTS (SELECT 1 FROM trimestre WHERE trmPfiID_fk = @v_trmPfiID_fk AND trmNom = @v_trmNom)\n\
    BEGIN\n\
        UPDATE trimestre\n\
        SET trmNbrTrav = @v_trmNbrTrav\n\
        WHERE trmPfiID_fk = @v_trmPfiID_fk AND trmNom = @v_trmNom\n\
    END\n\
ELSE\n\
    BEGIN\n\
        PRINT 'The script execution FAILED for record " (NR - 1) " (pfiID ' + @v_trmPfiID_fk + ', trimestre ' + @v_trmNom + ').'\n\
    END\n\
go\n\
"
}
EOF
) &lt; in.csv