Hi ,

I have below file with 13 columns. I need 2-13 columns seperated by comma and I want to append each row with a string "INSERT INTO xxx" in the begining as 1st column and then a variable "$node" and then $2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13 and at the end another string " ; COMMIT;"

It is not working . Can u please make this one work or new one .....

Code:

V1S="INSERT INTO schema.table VALUES (" <-- mostly constant value string
V1E=") ; commit ;" <-- mostly constant value string

node=$1 <-- passing value and varies (lets say here it is 10)

awk '{print $V1S $node ","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13 $V1E}' File1 > File2

Source_File:
-------------
0x0780000072F98E00 17 155 n/a 17 155 TAB1 SCH1 Perm 1 0 1 0
0x07800000747BF380 36 475 n/a 36 475 TAB1 SCH1 Perm 2 0 0 0
0x078000009A2A8600 36 476 n/a 36 476 TAB1 SCH1 Perm 11396 0 0 0

Expected Result in Target File:
-----------------------------
INSERT INTO schema.table VALUES (10,17,155,n/a,17,155,TAB1,SCH1,Perm,1,0,1,0); commit;
INSERT INTO schema.table VALUES (10,36,475,n/a,36,475,TAB2,SCH1,Perm,2,0,0,0); commit;
INSERT INTO schema.table VALUES (10,36,476,n/a,36,476,TAB2,SCH1,Perm,11396,0,0,0); commit;

Appreciate your help ...

Shyam

---------- Post updated at 11:24 AM ---------- Previous update was at 11:09 AM ----------

awk '{print "INSERT INTO scehma.table VALUES (" $2 "," $3 "," $4 "," $5 "," $6 "," $7 "," $8 "," $9 "," $10 "," $11 "," $12 "," $13 ") ; commit ;"}' File1 > File2

is working but how to get the "$node" varying variable value ???

you need to pass the variable in using something like:

awk -v node=$1 '{...

HTH

node=10
sed "s/\(.[^ \t]*\) \(.*\)/INSERT INTO schema.table VALUES($node \2/;s/ /,/4g;s/$/);commit;/" file

Thanks for the solution .. i am close but few more thingsg..

it is putting comma for every space in the result " see INSERT,INTO,schema.table,VALUES,(" and " 0,);commit ;"

Code
-------
V1S="INSERT INTO schema.table VALUES ("
V1E=");commit ; "
node=$1 ( here it is 10)
sed "s/\(.[^ \t]\) \(.\)/$V1S$node \2/;s/ /,/4g;s/$/$V1E/" File1 > File2
cat File2 | head -3
echo '' "

Result
--------
INSERT,INTO,schema.table,VALUES,(10,17,155,n/a,17,155,TAB1,SCH1,Perm,1,0,1,0,);commit ;
INSERT,INTO,schema.table,VALUES,(10,36,475,n/a,36,475,TAB2,SCH1,Perm,2,0,0,0,);commit ;
INSERT,INTO,schema.table,VALUES,(10,36,476,n/a,36,476,TAB3,SCH1,Perm,11396,0,0,0,);commit ;

One more thing , I also need ' " ' (double quotes) around 4th, 7th, 8th & 9th columns

We are close.. Something small is missing..
Shyam

its GNU sed