Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Forming an insert query using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Forming an insert query using awk
# 1  
Old 10-21-2010
Forming an insert query using awk
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  
Old 10-21-2010
MySQL Forming an insert query using awk
Quote:
Originally Posted by rakesh_s
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..
can you give some more information or a sample pattern which contains the information that you wan to put it in the sql statements??Smilie
# 3  
Old 10-21-2010
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  
Old 10-21-2010
MySQL Forming an insert query using awk
Input file:

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

Code:
#!/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

Smilie

output:
Code:
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  
Old 10-21-2010
code
Code:
awk -F "|" -v count=1000 -v x="'" '{count+=1;print  "insert into company values("count",\""$1"\","x $2 x",\""$4"\");" }' company


output
Code:
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");

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to insert new column with awk?

Hello guys, I'm new to shell programming, I would like to insert two new columns in a space separated file having this format using awk, starting from the third row: A B C D E F 1 ; A B C D E F 1 ; A B C D E F 1 ; A B C D E F 1 ; Basically, the resulting file should have the following... (3 Replies)
Discussion started by: transat
3 Replies

2. Shell Programming and Scripting

From sql Insert Query to XML format

Hi How do I translate Let say Cat inserts.sql gives Insert into PM9_TAXATION_ROUNDING (STATE_GECODE, TAX_TYPE, TAX_AUTHORITY, SYS_CREATION_DATE, SYS_UPDATE_DATE, APPLICATION_ID, DL_SERVICE_CODE, ROUNDING_METHOD) Values ('xx', 'xx', 'x', TO_DATE('10/26/2012 13:01:20',... (3 Replies)
Discussion started by: anuj87in
3 Replies

3. Shell Programming and Scripting

Insert query with shell variable with AWK

Hi, I'm a first timer with Unix so pardon my ignorance. I'm trying to read a comma separated file from the same folder where the script is and insert the value in a DB2 table. I'm using AWK for the same. I'm getting `)' not expected error. I'm not sure but for me it doesn't look like detailed... (8 Replies)
Discussion started by: Kabira Speaking
8 Replies

4. UNIX for Dummies Questions & Answers

Forming a query in unix level

Hi, I will execute a query in sqlplus and redirect to a file. The file will contains the date value such as 2011-04-12 02:00:00. i want to make the content of the file such as, select * from table where col1>to_date('2011-04-12 02:00:00','yyyy-mm-dd HH24:MI:SS'). Apar from the bold... (1 Reply)
Discussion started by: pandeesh
1 Replies

5. Programming

insert query help

Hello i want help to load data from file into mysql DB this part i know how to do but during loading i want to combine 2 fields into 1 field and insert into db as primary key in new column thanks advice how to do so (5 Replies)
Discussion started by: mogabr
5 Replies

6. Shell Programming and Scripting

forming group script from a text file

I am trying to make group of number of ID's. I have prepared following perl script but it does not provide any output. INPUT File 0174 0175 0176 0177 CODE: #!/usr/bin/perl -w $memlst = "/tmp/test.txt"; $membercnt = `wc -l $memlst`; $memhd = `head -1 $memlst`; $memcnt1 = 1; $class... (1 Reply)
Discussion started by: dynamax
1 Replies

7. Shell Programming and Scripting

How to use a variable in insert query?

My script contains as follows, VALUE=`sqlplus un/pwd <<EOF > OB.txt set pagesize 0 feedback off verify off heading off echo off select max(1) from table1; exit; EOF` insert into table2 values(1, 'The max value is $value',...); i need the value of VALUE to be inserted after 'The max... (2 Replies)
Discussion started by: savithavijay
2 Replies

8. UNIX for Dummies Questions & Answers

forming duplicate rows based on value of a key

if the key (A or B or ...others) has 4 in its 3rd column the 1st A row has to form 4 dupicates along with the all the values of A in 4th column (2.9, 3.8, 4.2) . Hope I explain the question clearly. Cheers Ruby input "A" 1 4 2.9 "A" 2 5 ... (7 Replies)
Discussion started by: ruby_sgp
7 Replies

9. Programming

SQL : Fine tune Insert by query

i would like to know how can i fine tune the following query since the cost of the query is too high .. insert into temp temp_1 select a,b,c,d from xxxx .. database used is IDS.. (1 Reply)
Discussion started by: expert
1 Replies

10. Shell Programming and Scripting

need to create a insert query for a file

Hi Guys, I need to create a insert query for the below file Fri Sep 4 06:25:51 2009 ACTION : 'CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1 ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)' DATABASE USER: '/' PRIVILEGE : SYSDBA CLIENT USER: oracle CLIENT TERMINAL: pts/3... (6 Replies)
Discussion started by: mac4rfree
6 Replies
Login or Register to Ask a Question