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;