Copy data from one database into another using bash

Question

Hello,

I need to copy data from one database into my own database, because i want to run it as a daily cronjob i prefer to have it in bash. I also need to store the values in variables so i can run various checks/validations on the values. This is what i got so far:

echo "SELECT * FROM table WHERE value='ABC' AND value2 IS NULL ORDER BY time" | mysql -u user -h ip db -p | sed 's/\t/,/g' | awk -F, '{print $3,$4,$5,$7 }' > Output  
cat Output | while read line
do
Value1=$(awk '{print "",$1}')
Value2=$(awk '{print "",$2}')
Value3=$(awk '{print "",$3}')
Value4=$(awk '{print "",$4}')
echo "INSERT INTO predb (value1,value2,value3,value4,value5) VALUES($Value1,$Value2,'$Value3',$Value4,'n')" | mysql -u rb db -p
done

I get the data i need from the database and store it in a new file seperated by spaces. Then i read the file line by line and store the values in variables, and last i run an insert query with the right varables.

I think something goes wrong while storing the values but i cant really figure out what goes wrong.

Answer 1

The awk used to get Value2, Value3 and Value4 does not get the input from $line. You can fix this as:

Value1=$(echo $line | awk '{print $1}')
Value2=$(echo $line | awk '{print $2}')
Value3=$(echo $line | awk '{print $3}')
Value4=$(echo $line | awk '{print $4}')       

Answer 2

There's no reason to call awk four times in a loop. That could be very slow. If you don't need the temporary file "Output" for another reason then you don't need it at all - just pipe the output into the while loop. You may not need to use sed to change tabs into commas (you could use tr, by the way) since awk will split fields on tabs (and spaces) by default (unless your data contains spaces, but some of it seems not to).

echo "SELECT * FROM table WHERE value='ABC' AND value2 IS NULL ORDER BY time" | 
    mysql -u user -h ip db -p | 
    sed 's/\t/,/g' |                 # can this be eliminated?
    awk -F, '{print $3,$4,$5,$7 }' | # if you eliminate the previous line then omit the -F,
    while read line
    do
        tmparray=($line)
        Value1=${tmparray[0]}
        Value2=${tmparray[1]}
        Value3=${tmparray[2]}
        Value4=${tmparray[3]}
        echo "INSERT INTO predb (value1,value2,value3,value4,value5) VALUES($Value1,$Value2,'$Value3',$Value4,'n')" | mysql -u rb db -p
    done

That uses a temporary array to split the values out of the line. This is another way to do that:

        set -- $line
        Value1=$1
        Value2=$2
        Value3=$3
        Value4=$4