Shell Programming and Scripting

Hi,

I have a situation where I need to create a SQL statement using Unix script for each value in first field (ID). The file looks like this.

Id,TARGET_FIELD_NAME,SOURCE_FIELD_NAME
1,Test_Rate,Field1
1,Test_Factor,Field2
1,Test_Size,Field3
2,Test_Rate,Field4
2,Test_Factor,Field5
2,Test_Size,Field6

So I need to create a insert script which would look like this.

INSERT INTO MYTABLE (<List Column3>) SELECT <List Column5> SOURCE;


Expected output -
ID - 1:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field1, Field2, Field3 FROM SOURCE;


ID - 2:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field2, Field3, Field4 FROM SOURCE;

The Current code I have generates this output, but I am unable to use the loop variable in awk. (See highlighted)

Code:

Id=`awk -F"," '{print $1}' AboveFile.txt | sort -u`
for i in $Id
do

insertlist=`awk -F"," '{if ($1 == 1) print $2 ","}' AboveFile.txt`
selectlist=`awk -F"," '{if ($1 == 1) print $3 ","}' AboveFile.txt`

echo "INSERT INTO MYTABLE ($insertlist) SELECT $selectlist FROM SOURCE;"

done

I am unable to parameterize the highlighted part of the code to use the loop variable i.

insertlist=`awk -F"," '{if ($1 == $i) print $2 ","}' AboveFile.txt`
selectlist=`awk -F"," '{if ($1 == $i) print $3 ","}' AboveFile.txt`

If there is anyway I can do this, I will be able to get my expected result. Is there a way I can parameterize this awk statement to use the loop variable? Please suggest.

Thanks in advance for sharing your thoughts

---------- Post updated at 11:02 PM ---------- Previous update was at 11:00 PM ----------

Sorry, I noticed that the expected result is wrong for ID2.
Here is the right expected result -

Expected output -
ID - 1:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field1, Field2, Field3 FROM SOURCE;


ID - 2:
INSERT INTO MYTABLE (Test_Rate, Test_Factor, Test_Size) SELECT Field4, Field5, Field6 FROM SOURCE;

try..

Code:

awk -F, 'NR>1{A[$1]=A[$1]","$2;B[$1]=B[$1]","$3}END{for(i in A){sub(",","",A[i]);sub(",","",B[i]);print "INSERT INTO MYTABLE ("A[i]") SELECT "B[i]" FROM SOURCE;"}}' filename

Another approach:

Code:

awk -F, '
        NR > 1 {
                C[$1] = C[$1] ? C[$1] OFS $2 : $2
                F[$1] = F[$1] ? F[$1] OFS $3 : $3
        }
        END {
                for ( k in C )
                {
                        print "ID - " k ":"
                        print "INSERT INTO MYTABLE (" C[k] ") SELECT " F[k] " FROM SOURCE;"
                }
        }
' OFS=, file

Parameter can be hacked into awk like this

Code:

insertlist=`awk -F"," '{if ($1 == '"$Id"') print sep $2; sep=","}' AboveFile.txt`

or passed like this

Code:

insertlist=`awk -F"," '{if ($1 == id) print sep $2; sep=","}' AboveFile.txt id="$Id"`

or

Code:

insertlist=`awk -v id="$Id" -F"," '{if ($1 == id) print sep $2; sep=","}' AboveFile.txt`

Your method is inefficient: if your file is long it is read too often.