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