CODEWITHSUNDEEP
mysqlsqlbash
user1365951
user1365951

Reputation: 21

How can I insert data into tables from variable text files?

I want to know how can I insert data into tables(exists on sql server) from variable text files.

At first time, I tried to use LOAD DATA INFILE command like this :

mysql --user=user --password=password --database=database<<EOF
LOAD DATA INFILE 'test.txt' INTO TABLE example 
FIELD TERMINATED BY ',' LINES TERMIANATED BY '\n';
EOF

It was working. However, as you can see, the text file is fixed in script on this way. So I think this structure secondly.

cat "${FILE}" | while read line ; do
 #a valid INSERT command

echo ${INSERT} | mysql --user=user --password=password --database=database
done

I want to construct a valid INSERT command from each line but I don't know what to do.

Do you have any idea? Thanks.

Upvotes: 1

Views: 1518

Answers (1)

Burton Samograd
Burton Samograd

Reputation: 3638

You can expand variables in your here documents like the follow example which defines a function that allows you to specify the input file to load and the table to load it into:

load_file_into_db() {
    infile=$1
    table=$2
    mysql --user=user --password=password --database=database<<EOF
    LOAD DATA INFILE '${infile}' INTO TABLE ${table} 
    FIELD TERMINATED BY ',' LINES TERMIANATED BY '\n';
    EOF
}

# Then you can call the function like so
load_file_into_db text.txt example

Variables can always be expanded into here documents unless you enclose the termination character in single quotes (ie. if you use 'EOF' instead of EOF before the <<)

Upvotes: 1

Related Questions