Forming an insert query using awk

Shell Programming and Scripting
1

Hi,

I'm trying to form an insert sql query using shell programming. I have table named company with four columns 'company name', 'company id', 'company code' and 'last change id'

I have to read the company name, company code and last change id from a file delimited by | which has around 10 companys...The company id has to be incremented from say 1000, 1001, 1002 upto 1010...

My output should be an insert statement like

insert into company values (1000, "ABC company", 'E', "Last_user");
insert into company values (1001, "EFG company", 'A', "Last_user");
.
.
insert into company values (1010, "XYZ company", 'D', "Last_user");

Please let me know how can i do this...Also let me know if you need additional info..

2

can you give some more information or a sample pattern which contains the information that you wan to put it in the sql statements??:slight_smile:

3

My input file will look like this...

ABC company | Joseph kiely | 558-445-5256 | joseph.keily@xxx.com
CDEF company | Jason Stat | 521-457-5695 | jason.stat@xrt.com

I basically need to create an insert statment using the above values...I know to use awk and get the above values into the insert statement..but i need to increment the company id by one for each insert statement...Can you help

4

Input file:

ABC company | Joseph kiely | 558-445-5256 | joseph.keily@xxx.com
CDEF company | Jason Stat | 521-457-5695 | jason.stat@xrt.com

#!/bin/sh
id=1000
while read details
do
comapnyname=`echo $details | awk -F "|" '{print $1}'`
name=`echo $details | awk -F "|" '{print $2}'`
phonenumber=`echo $details | awk -F "|" '{print $3}'`
email=`echo $details | awk -F "|" '{print $4}'`
echo "insert into company values (${id},\"${comapnyname}\",\"${name}\",\"${phonenumber}\",\"${email}\");"
id=`expr ${id} + 1`
done < example

:slight_smile:

output:

insert into company values (1000,"ABC company "," Joseph kiely "," 558-445-5256 "," joseph.keily@xxx.com");
insert into company values (1001,"CDEF company "," Jason Stat "," 521-457-5695 "," jason.stat@xrt.com");

i hope this is what you wanted...

5

code

awk -F "|" -v count=1000 -v x="'" '{count+=1;print  "insert into company values("count",\""$1"\","x $2 x",\""$4"\");" }' company

output

insert into company values(1001,"ABC company ",' Joseph kiely '," joseph.keily@xxx.com");
insert into company values(1002,"CDEF company ",' Jason Stat '," jason.stat@xrt.com");