sed string manipulation in shell script

Hello,

I have 1000 of sql queries and i need to push column value in query. e.g.

SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');

I need to put id column value for those sql queries. Resultant queries should be

INSERT INTO test (id,name) VALUES (1,'a');
INSERT INTO test (id,name) VALUES (2,'b');
INSERT INTO test (id,name) VALUES (3,'c');
INSERT INTO test (id,name) VALUES (4,'d');

I wrote shell script for that

# !/bin/bassh

ID=`cat test.sql | grep INSERT_ID | awk -F"=" '{print $2}' | sed 's/\(.*\)....../\1/'`
VALUES=`cat test.sql | grep "INSERT INTO test"

for ff in $VALUES
do

        for f in $ID
        do
        sed "s/VALUES (/VALUES ($f,/" $ff > finalfile.sql
        done
done

Where ID variable contains values 1,2,3,4... and so on
and VALUES variable contains SQL queries are

INSERT INTO test (id,name) VALUES ('a');
INSERT INTO test (id,name) VALUES ('b'); and so on

I applied nested loop and finally trying to push id column values. but it errors "filename too long". Following is the command

sed "s/VALUES (/VALUES ($f,/" "$ff" > finalfile.sql

command after value substituion

sed "s/VALUES (/VALUES (1,/" "INSERT INTO test (id,name) VALUES ('a');"

"Filename too long" error occus on %ff variable value i.e. on sql qurey.

Can someone please help how to accomplish it ?

Thank you.

Try:

awk -F"[ =]" '
/^SET/{id="(" $3 ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file

Maybe this will work for you?

while read line; do
if echo "$line" | grep ^SET ; then
id=$(echo "$line" | cut -d= -f2)
else
echo "$line" | sed "s/VALUES (/VALUES ($id,/"
fi
done <input | sed '/^SET/d;'

 # cat dat
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');

 
# ./sc
INSERT INTO test (id,name) VALUES ('1,a');
INSERT INTO test (id,name) VALUES ('2,b');
INSERT INTO test (id,name) VALUES ('3,c');
INSERT INTO test (id,name) VALUES ('4,d');

 # cat sc

#!/bin/bash
id=$( (sed -n '/=/p' dat | sed 's/SET INSERT_ID=//') )
char=$( (sed -e "s/.*VALUES ('\([a-z]\)');/\1/" -e "/SET INSERT/d" dat) )
idgr=( $id ) ; chargr=( $char )
idcount=`echo $id | tr -d [:space:]` ; count=${#idcount}
while [ $((count -= 1 )) -gt -1 ]
  do
    sed -e "s/('${chargr}')/('${idgr},${chargr}')/" -e '/^SET INSERT_ID/d' dat | sed -n "/('${idgr},${chargr}')/p"
    ((++i))
  done

Hello,

None of the commands work for me. Anyways when i run following on commandline gives me error. Please let me know how to execute this

 
sed -e 's/VALUES (/VALUES (4255889,/' "INSERT INTO test (id,name) VALUES ('A');

Error: File name too long

Desire result should be

INSERT INTO test (id,name) VALUES (4255889,'A');

Also is there any way to tell shell script to ignore error and continue execution. Like

INSERT INTO test (id,name) VALUES (1,'A');
INSERT INTO test1 (id,name) VALUES (1,'A');
INSERT INTO test (id,name) VALUES (2,'A');

If test1 not exist .. script should continue and enter records for next table. I have only table in database which is test and test1 not exist. when i try to execute sql file through shell script. It gives me error that test1 table not exist and shell script terminates.

I want shell script continue execution and enter next records from sql file and ignore errors for tables which not exist in database.

Thank you.

Hi mirfan
Did you exactly like this? I m not clear..

my input must be INSERT INTO test (id,name) VALUES (4255889);
Please corret me if it is wrong

echo "INSERT INTO test (id,name) VALUES (4255889);" |
> sed "s/VALUES (4255889)/VALUES (4255889,'A')/"
INSERT INTO test (id,name) VALUES (4255889,'A');

like this

 
sed "s/my pattern that so i want to change this / change with /"

What error or output did you get?

This is my output with your input file:

$ cat file
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');
$ awk -F"[ =]" '
/^SET/{id="(" $3 ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file
INSERT INTO test (id,name) VALUES (1,'a');
INSERT INTO test (id,name) VALUES (2,'b');
INSERT INTO test (id,name) VALUES (3,'c');
INSERT INTO test (id,name) VALUES (4,'d');

Hello,

Both of the solution(ygemici & Franklin52) works for simple queries metioned earlier.
But i have very long queries sql file contains 1000 of database queries. e.g.

cat sourcefile.sql

SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);

This should be converted into as follows outfile.sql, where column1value is SET INSERT_ID

INSERT INTO table1 (column1,column2..column34) VALUES (column1value,column2value..column34value);

INSERT INTO table2 (column1,column2..column23) VALUES (column1value,column2value..column23value);

When i tried Franklin52 solution doesn't seem to work on mentioned queries because every table in sourcefile.sql contains different tables and no. of columns.

While ygemici your solution works from command line but when i run from shell script it gives me error "Filename too long".

Here is my shell script

# !/bin/bash

IDS=`cat sourcefile.sql | grep INSERT_ID | awk -F"=" '{print $2}' | sed 's/\(.*\)....../\1/'`    # all INSERT_IDS are fetched in array
LINES=$(cat sourcefile.sql | wc -l) # total no. of lines in sourcefile.sql

for i in $ID
do
        for (( i=2; i<=$LINES; i=$i+2 ))
        do
        QUERY=`head -$i sourcefile.sql | tail -1`
        MQUERY=`echo 'echo' "\"$QUERY ($f);\"|"`
        SUB=`echo "sed \"s/VALUES (/VALUES ($f,/\""`
        FINALQ=`"echo \"$QUERY\" | $SUB"`
        eval $FINALQ   # gives error "Filename too long"
        done
done

If you don't have spaces between the values this should work:

awk -F"[ =]" '
/^SET/{id="(" int($3) ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file

Otherwise:

awk -F"(" '
/^SET/{sub(".*=",""); id=int($0) ","}
/^INSERT/{$3=id $3;print}
' OFS="(" file

Hi,

Thanks great. second solution for me.
Thanks for the help

Thank you.

Then
for example let my file is like below

 # cat mysql
SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);

SET INSERT_ID=3;
INSERT INTO table3 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=4;
INSERT INTO table4 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=5;
INSERT INTO table5 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=6;
INSERT INTO table6 (column1,column2..column34) VALUES (column2value..column34value);

 # ./changex
INSERT INTO table1 (column1,column2..column34) VALUES (1,column2value..column34value);
INSERT INTO table2 (column1,column2..column23) VALUES (2,column2value..column23value);
INSERT INTO table3 (column1,column2..column34) VALUES (3,column2value..column34value);
INSERT INTO table4 (column1,column2..column34) VALUES (4,column2value..column34value);
INSERT INTO table5 (column1,column2..column34) VALUES (5,column2value..column34value);
INSERT INTO table6 (column1,column2..column34) VALUES (6,column2value..column34value);

 # cat changex
 
#!/bin/bash
id=$( (sed -n '/=/p' mysql | sed -e 's/SET INSERT_ID=//' -e 's/;//') ) ; idgr=( $id )
idcount=`echo $id | tr -d [:space:]` ; count=${#idcount}
sed -e '/SET INSERT_ID/d' -e '/^$/d' mysql > mysqltmp
rm -f mysqlnew
for ix in ${idgr[@]}
   do
     sed -n "/INSERT.*table$ix.*VALUES/s/\(column2value.*\));$/$ix,\1);/p" mysqltmp >> mysqlnew
   done
cat mysqlnew
 

Taken from the original poster's two data samples:

$ cat data
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');
SET INSERT_ID=5;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=6;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);

Relatively simple sed solution:

$ sed '/^SET INSERT_ID=/{s/;*$//;h;d;}; /VALUES/{G;s/VALUES (\(.*\)\(\nSET INSERT_ID=\)\(.*\)/VALUES (\3,\1/;}' data
INSERT INTO test (id,name) VALUES (1,'a');
INSERT INTO test (id,name) VALUES (2,'b');
INSERT INTO test (id,name) VALUES (3,'c');
INSERT INTO test (id,name) VALUES (4,'d');
INSERT INTO table1 (column1,column2..column34) VALUES (5,column2value..column34value);

INSERT INTO table2 (column1,column2..column23) VALUES (6,column2value..column23value);

Regards,
Alister

Great. All of the solutions worked for me.
Now finally i had some table data as follows in input file.

SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value),(column2value..column34value),(column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value),(column2value..column23value);

I need output file as INSERT_ID should be inserted at where column value starts after comma (,).

INSERT INTO table1 (column1,column2..column34) VALUES (INSERT_ID,column2value..column34value),(INSERT_ID,column2value..column34value),(INSERT_ID,column2value..column34value);
here INSERT_ID is 1

INSERT INTO table2 (column1,column2..column23) VALUES (INSERT_ID,column2value..column23value),(INSERT_ID,column2value..column23value);
and here INSERT_ID is 2

Thanks in advance.