Bash script to insert values in MySQL

Question

I want to make a bash script that connects to my MySQL server and inserts some valuse from a txt file. I have written this down:

#!/bin/bash
echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('cat test.txt');" | mysql -uroot -ptest test;

but I'm recieving the following error:

ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1

I suppose the error is in my txt file, but I've tried many variations and still no hope of success.

My txt file looks like this:

10.16.54.29 00:f8:e5:33:22:3f marsara

Answer 1

Try this one:

#!/bin/bash
inputfile="test.txt"
cat $inputfile | while read ip mac server; do
    echo "INSERT INTO test (IP,MAC,SERVER) VALUES ('$ip', '$mac', '$server');"
done | mysql -uroot -ptest test;

This way you streaming the file read as well the mysql comand execution.