Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Regarding file input to SQL from command line

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Regarding file input to SQL from command line
# 8  
Old 11-29-2015
Maximum command line length (in Linux)
Code:
getconf ARG_MAX

This User Gave Thanks to jgt For This Post:
Showdown
# 9  
Old 11-30-2015
How about
Code:
 { tr $'\n' ' ' < $1; echo; } | fold -w80 | while read ID; do ID=${ID// /,}; echo "update table_name  set status='INACTIVE' where req_id in ($ID);" ; done
update table_name  set status='INACTIVE' where req_id in (001,002,003,004,005,006,007,008,009,010,011,012,013,014,015,016,017,018,019,020);
update table_name  set status='INACTIVE' where req_id in (021,022,023,024,025,026,027,028,029,030,031,032,033,034,035,036,037,038,039,040);
update table_name  set status='INACTIVE' where req_id in (041,042,043,044,045,046,047,048,049,050,051,052,053,054,055,056,057,058,059,060);
update table_name  set status='INACTIVE' where req_id in (061,062,063,064,065,066,067,068,069,070,071,072,073,074,075,076,077,078,079,080);
update table_name  set status='INACTIVE' where req_id in (081,082,083,084,085,086,087,088,089,090,091,092,093,094,095,096,097,098,099,100);
update table_name  set status='INACTIVE' where req_id in (101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120);
.
.
.

Save that to a file and have SQL run it.
This User Gave Thanks to RudiC For This Post:
Showdown
# 10  
Old 12-01-2015
Thanks Everyone for your help. In the meantime i got permission to split the file based upon no of lines and moved the code successfully to prod.

Anyway I learn few new thing from the discussion and extend my thanks again to you.

---------- Post updated at 12:21 PM ---------- Previous update was at 11:34 AM ----------

Hi RudiC,

Your approach is quite straight forward and simple. I was just curious to do that in dev
but upon executing the below command
Code:
{ tr $'\n' ' ' < to_pivot.txt; echo; } | fold -w80

the fold functionality seems to be not working rather it is just taking one one id each. If i remove the dollar($) and keep the \n then the fold is correctly working.

The command to format the ids to be comma separated inside the do while loop is throwing the below error.

Code:
test11.sh: ID=${ID// /,}: The specified substitution is not valid for this command.

Finally the fold will wrap the lines to 80 chars irrespective of whether the id is complete or in complete.

update table_name set status='INACTIVE' where req_id in (11682 10550 11105 8855 11674 11670 11668 11666 11702 10841 11744 11743 11574 11);
update table_name set status='INACTIVE' where req_id in (573 11549 11548 11118 8376 11116 11115 9910 11581 11567 11793 11798 4642 11800 1);
can you please suggest me to move.
Many Thanks.

---------- Post updated at 01:12 PM ---------- Previous update was at 12:21 PM ----------

Hi RudiC,

I have done some changes to the above method and now it itt working perfectly. Many Thanks

Code:
{ tr '\n' ' ' < to_pivot.txt; echo; } | fold -s | while read ID; do ID=`echo ${ID}|sed 's/ /,/g'`; echo "update table_name  set status='INACTIVE' where req_id in ($ID);" >> form_sql.sql ; done
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Read input file and used it to SQL query

Hi All, Seeking for your assistance to read each line $1 and $2 of input file and used it to query. Ex. file1.txt(number range) 9064500000 9064599999 9064600000 9064699999 9064700000 9064799999 Database name: ranges_log a_no message 9064500001 test 9064700000 ... (7 Replies)
Discussion started by: znesotomayor
7 Replies

2. Shell Programming and Scripting

Pass command line arg to sql file

Hi all, How to pass the command line argument to a sql file Script: #!/bin/ksh if ] ; then test.sql fi My Sql Informix DB: echo "select * from table where col1 = 2234 and col2 = '$3'"|dbaccess ddname But im getting `:' unexpected error (5 Replies)
Discussion started by: Roozo
5 Replies

3. Shell Programming and Scripting

Read input file with in awk script not through command line

Hi All, Do we know how to read input file within awk script and send output toanother log file. All this needs to be in awk script, not in command line. I am running this awk through crontab. Cat my.awk #!/bin/awk -f function test(var){ some code} { } END { print"test code" } (5 Replies)
Discussion started by: random_thoughts
5 Replies

4. Shell Programming and Scripting

Need help to run sql query from a script..which takes input from a file

I need to run sql script from shell script which takes the input from a file and contents of file will be like : 12345 34567 78657 and query will be like : select seq_nbr from bus_event where event_nbr='12345'; select seq_nbr from bus_event where event_nbr='34567'; select seq_nbr... (1 Reply)
Discussion started by: rkrish
1 Replies

5. UNIX for Dummies Questions & Answers

SQL Script to use variable value from input file

Here is the requirement, When I run the "run file KSH (sql)", it should substitute '${pCW_Bgn_DT}' with 201120 and '${pCW_End_DT}' with 201124 Input File ---------- $ cat prevwk.dat 201124 20110711 run file KSH (sql) ------------------ In this file, I want to use the variables... (1 Reply)
Discussion started by: shanrice
1 Replies

6. UNIX for Dummies Questions & Answers

Bash script to delete file input on command line

1) I wrote a script and gave the desired permissions using "chmod 755 scriptname". Now if i edit the script file, why do i need to set the permission again? Didn't i set the permission attribute.. or if i edit the file, does the inode number of file changes? 2) I am running my unix on a server... (1 Reply)
Discussion started by: animesharma
1 Replies

7. Shell Programming and Scripting

SQL PLUS Command 'ACCEPT' is not waiting for user input with sh shell script

Dear All, The sqlplus 'Accept' command is not waiting for user input when I include the command within a shell script. Note: The 'Accept' command is working fine if I execute it in a SQLPLUS Prompt. Please fins the below sample script which i tried. SCRIPT: -------- #!... (4 Replies)
Discussion started by: little_wonder
4 Replies

8. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

9. Shell Programming and Scripting

isql input file with multiple sql statements

I've got: isql -U $USERID -S $SERVER -D $DATABASE -i inputfile.sql -o outputfile.txt in inputfile I have: go sql#1 go sql#2 go sql#3 go I also tried without "go" and with";" instead which did not work SQL statements will work if I paste them directly into the script and use EOF ... (0 Replies)
Discussion started by: Cailet
0 Replies

10. Shell Programming and Scripting

SED + Regex + SQL Input file

Here's the problem... I have a mysqldump file and I need to put single quotes around the date/time timestamp. So for example I have a line like: INSERT INTO attachments VALUES (1,182,2004-08-06 09:24:04,'description'... and I need it to become INSERT INTO attachments VALUES... (10 Replies)
Discussion started by: primal
10 Replies
Login or Register to Ask a Question