Generate sql statement using shell scripting

vinus · January 30, 2016, 9:26pm

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 !

vinus · February 1, 2016, 10:36am

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.

#
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

vinus · February 1, 2016, 10:41am

Can anyone please assist? I need to write the code in ksh.

jgt · February 1, 2016, 12:05pm

# 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.

vinus · February 1, 2016, 12:47pm

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.

jgt · February 1, 2016, 1:33pm

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.

# 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                                               
#

vinus · February 1, 2016, 1:50pm

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.

RudiC · February 1, 2016, 2:43pm

How about

awk -F, -v SQ="'" '
FNR == NR       {if (!/^#/) C[NR] = $0
                 next
                }
                {print "UPDATE EMP SET "
                 for (c in C) print "\t" C[c] " = " SQ $c SQ ","
                 print  "WHERE empno = " $1 ";"
                 print "commit;"
                }
' file[12]
UPDATE EMP SET 
    ename = 'KING',
    job = 'PRESIDENT',
    salary = '2000',
    deptno = '10',
WHERE empno = 7839;
commit;
UPDATE EMP SET 
    ename = 'BLAKE',
    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 = '11 ',
WHERE empno = 7843;
commit;

Please note that the where - clause is using an invariable, constant column name as you didn't supply any means to identify the column to use.

vinus · February 2, 2016, 12:43pm

Thank you so much Rudic ! Its working as expected. I'm testing the code with more files and let you know if i find any issues.

Thanks for ur help!

---------- Post updated 02-02-16 at 01:43 PM ---------- Previous update was 02-01-16 at 04:13 PM ----------

Rudic,
Thanks for your help ! I found one minor issue while testing the code. Currently i'm getting output like this. I need to remove last comma before WHERE in SQL update . My sql will not work if there is a comma in the end of SET command.

UPDATE EMP SET 
    ename = 'JONES',
    job = 'PROGRAMMER',
    salary = '550',
    deptno = '11',
WHERE empno = 7842;

desired output:

UPDATE EMP SET 
    ename = 'JONES',
    job = 'PROGRAMMER',
    salary = '550',
    deptno = '11'
WHERE empno = 7842;

I'm trying other option to remove last comma before WHERE in SQL update using sed command. Please assist?
Thanks

RudiC · February 2, 2016, 1:01pm

It's a bit difficult to remove chars from a line already printed, so I'd propose to do the whole thing in one single line. Would this do?

awk -F, -v SQ="'" '
FNR == NR       {if (!/^#/) C[NR] = $0
                 next
                }
                {DL = ""
                 printf "UPDATE EMP SET"
                 for (c in C)   {printf "%s %s = %s%s%s", DL, C[c], SQ, $c, SQ
                                 DL = FS
                                }
                 printf  " WHERE empno = %s;\n", $1
                 print "commit;"
                }
' file[12]
UPDATE EMP SET ename = 'KING', job = 'PRESIDENT', salary = '2000', deptno = '10' WHERE empno = 7839;
commit;
UPDATE EMP SET ename = 'BLAKE', 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 = '11 ' WHERE empno = 7843;
commit;

---------- Post updated at 19:01 ---------- Previous update was at 19:00 ----------

I think SQL does accept one line UPDATE statements, doesn't it?

vinus · February 2, 2016, 2:22pm

Thanks Rudic !

Yes, the SQL does accept one line UPDATE statements. I tried your code with few columns and its working as expected. I think the sql will work even if we have one line update statements with more columns ( ex: 30 to 40). if there is a problem i can use sed command to remove the last comma after we generate the update statement.

Thank u so much for your help !