Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Generate sql statement using shell scripting

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Generate sql statement using shell scripting
# 1  
Old 01-30-2016
Generate sql statement using shell scripting
Can anyone please assist me?
I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if inputfile2 has 5 rows, then we should generate 5 update statements because column1 is unique. inputfile1 and inputfile2 may contain more columns. I have added only few columns in my sample files. The output file should generate the sql update statements for the uncommented columns, but the positions should be correct.

Please let me know if you need any other clarification.

Thanks !
Inputfile1.txt (79 Bytes) 
Inputfile2.txt (203 Bytes) 
output.txt (589 Bytes) 
# 2  
Old 02-01-2016
Shell scripting - - need help
Can anyone please assist me?
I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if input_file2 has 5 rows, then we should generate 5 update statements because column1 is unique. input_file1 and input_file2 may contain more columns. I have added only few columns in my sample files. The output file should generate the sql update statements for the uncommented columns, but the positions should be correct.

Code:
#
INPUTFILE1:

#empno  
ename 
job    
#manager    
salary     
#commision   
deptno

Input _file2:

7839,KING,PRESIDENT,MANAGER,2000,100,10
7840,BLAKE,ANALYST,MANAGER1,1000,50,13
7841,CLARK,PROGRAMMER,MANAGER2,500,40,13
7842,JONES,PROGRAMMER,MANAGER,550,40,11
7843,SCOTT,ANALYST,MANAGER,550,40,11 

Desired output: 
UPDATE EMP
SET ename='KING',
    job='PRESIDENT',
    salary='2000',
    deptno='10'
WHERE empno=7839;
commit;
UPDATE EMP
SET ename='BLACK',
    job='ANALYST',
    salary='1000',
    deptno='13'
WHERE empno=7840;
commit;
UPDATE EMP
SET ename='CLARK',
    job='PROGRAMMER',
    salary='500',
    deptno='13'
WHERE empno=7841;
commit;
UPDATE EMP
SET ename='JONES',
    job='PROGRAMMER',
    salary='550',
    deptno='11'
WHERE empno=7842;
commit;
UPDATE EMP
SET ename='SCOTT',
    job='ANALYST',
    salary='550',
    deptno='40'
WHERE empno=7843;
commit;



Please let me know if you need any other clarification.
Thanks
Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) when posting full-line and, especially, multi-line sample input, output, and code segments.

Do not bump up posts.

And, do not post the same question in two threads.

I will merge these threads in a few minutes.
Last edited by vinus; 02-01-2016 at 01:44 PM.. Reason: Change ICODE tags to CODE tags. Merge threads.
# 3  
Old 02-01-2016
Can anyone please assist? I need to write the code in ksh.
Last edited by vinus; 02-01-2016 at 12:48 PM..
# 4  
Old 02-01-2016
Code:
# cat vinus                                            
IFS=,                                                  
while read emp name position jobclass salary comm dept 
do                                                     
        echo UPDATE EMP                                
        echo "SET ename='$name',"                      
        echo "\tjob='$position',"                      
        echo "\tsalary='$salary',"                     
        echo "\tdeptno='$dept',"                       
        echo "WHERE empno='$emp';"                     
        echo "commit;"                                 
done <vinus2                                           
# cat vinus2                                           
7839,KING,PRESIDENT,MANAGER,2000,100,10                
7840,BLAKE,ANALYST,MANAGER1,1000,50,13                 
7841,CLARK,PROGRAMMER,MANAGER2,500,40,13               
7842,JONES,PROGRAMMER,MANAGER,550,40,11                
7843,SCOTT,ANALYST,MANAGER,550,40,11                   
#

The \t in the echo statements is interpreted as a <tab> in SysV unix, in Linux, replace it with a <tab> character.
As an aside, I only did this because I feel sorry for your boss.
# 5  
Old 02-01-2016
Thanks for your help!

I have to create the update statements for the uncommented columns from inputfile1. Also the file may contain n number of columns, so dynamically need to build the query. I would really appreciate if someone can help me to solve this problem.
Last edited by vinus; 02-01-2016 at 02:02 PM..
# 6  
Old 02-01-2016
I assumed that you mean that the input file can have more fields in it than are required by the sql, and that they may not always be in the same order.
I tried doing the following with a comma delimited input file but I could not get it to work, but it does work with a space or tab delimited file.
I found a use for this, as I have a client that sends us an excel file that we convert to csv and invariably there are extraneous columns in the file.
Code:
# cat vinus4                                                        
7839    KING    PRESIDENT       MANAGER 2000    100     10          
7840    BLAKE   ANALYST MANAGER1        1000    50      13          
7841    CLARK   PROGRAMMER      MANAGER2        500     40      13  
7842    JONES   PROGRAMMER      MANAGER 550     40      11          
7843    SCOTT   ANALYST MANAGER 550     40      11                  
# cat vinus3                                                        
echo $*                                                             
while read $*                                                       
do                                                                  
        echo $emp $name $position $dept                             
done<vinus4                                                         
                                                                    
# ./vinus3 emp name position jobclass salary comm dept              
emp name position jobclass salary comm dept                         
7839 KING PRESIDENT 10                                              
7840 BLAKE ANALYST 13                                               
7841 CLARK PROGRAMMER 13                                            
7842 JONES PROGRAMMER 11                                            
7843 SCOTT ANALYST 11                                               
#

# 7  
Old 02-01-2016
Thanks for ur help!

Yes, the input file may contain more columns. Ex: if inputfile1(contain column names) has 7 columns, then the data file (inputfile2) will also contain the same number of columns as inputfile1. The data will be in the correct order. The data file doesn't contain the header names. Also here the main issue is that the output file should not contain the update statements for the commented columns.
Note: The commented lines will be in any positions so we cannot hardcode it in the code.
Last edited by vinus; 02-01-2016 at 02:58 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell scripting with case statement

Foe example we have three environments int,qa and prod.Each environment has some number of servers. int=Server1,Server2,Server3 qa=Server4,Server5,Server6 prod=Server7,Server8,Server9 echo "Enter the Environment i.e int,qa,prod" read env case $env in int) ## Need command where all the... (9 Replies)
Discussion started by: nareshreddy443
9 Replies

2. Shell Programming and Scripting

Using shell to generate case statement

Hi Gurus, I have a very weird requirement and have no clue to resolve the issue. please help me get out this difficulty below two tables, table1 contains the column name. D means this column used for the rule. for example: rule 0 is all columns have value, rule1 is col3 and col7 have no value.... (2 Replies)
Discussion started by: Torhong
2 Replies

3. Shell Programming and Scripting

How to create SQL statement out of data using shell script?

Table TAB1 contains following example data (its a tree sitting in table data format & its driven based CHILD & PARENT column pick the RULE condition to generate the below SQL: CHILD PARENT SS MID MNM VNM RULE FLG 1 ? S1 ? ? V1 rule004 I 2 1 S1 ? ? V1 0 Z 3 1 S1 ? ? V1 1 Z ... (6 Replies)
Discussion started by: gksenthilkumar
6 Replies

4. Shell Programming and Scripting

Korn shell script - SQL statement challenges

Hi scripting experts. I have some coding challenges that I'm hoping you can help me out. I have one file#1 that contains the following sql statement that spans over multiple lines: sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id... (1 Reply)
Discussion started by: pchang
1 Replies

5. UNIX for Dummies Questions & Answers

BASH Shell Scripting: If, Then Statement

I'm having trouble trying to create a BASH shell script. I want the user to input a command "cat file_name.c" and then the shell script will delete all comments "/* */" from file_name.c else exit. So far I have this: #!/bin/bash read "cat file" // User will input command cat... (7 Replies)
Discussion started by: inkjoy00
7 Replies

6. Linux

Shell Script to generate Dynamic Param file Using SQL Plus Quey

Hi All, Can anyone give me Shell script sample script to generate Param file by Reading Values from SQL Plus query and it should assign those values to variables like.. $$SChema_Name='ORCL' Thanks in Advance... Srav... (4 Replies)
Discussion started by: Sravana Kumar
4 Replies

7. Shell Programming and Scripting

Unix shell command output to a sql statement

Can i do this Say one command sed 's/:*/ /g' $summf is returning C1234 C2345 C3434 some no of rows, now this ouput i have to insert it into a DB table how do i do this?? (2 Replies)
Discussion started by: depakjan
2 Replies

8. Shell Programming and Scripting

generate tabular output from an input text file in unix shell scripting

Hi, I have the output (as below) which i want it to be in a table. For e.g. space utilization in PSE on path /logs is 0% space utilization in PSE on path /logs/tuxedo/tuxlsp is 16% space utilization in PSE on path /ldvarlsp/lsp/log is 37% space utilization in PSE on path /home is 6%... (7 Replies)
Discussion started by: pkbond
7 Replies

9. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

10. Shell Programming and Scripting

Sql with shell scripting

Hi, I want to extract data from database table to a flat file using shell scripting. For loading data from file to a table, I had used SQL Loader. How can I do the vice versa using shell scripting. Thanks in advance (3 Replies)
Discussion started by: sendhil
3 Replies
Login or Register to Ask a Question