How to load data to tabel in MySql using script

maheshdf · 11-29-2006, 04:10 AM

Hi,

I want to upload some date to table in my MySQL database. Now I am using perl coding to do it. Can some one give me and idea how to do it using shell script.

my sql statement as follows,

Code:

LOAD DATA LOCAL INFILE '/Scripts/tableupload//20051101.cdr.txt' INTO TABLE ppscdr_200511 
FIELDS TERMINATED BY '|';

zaichik · 11-29-2006, 08:06 AM

You could just call the mysql client with -e and pass it the commands you want executed. For example:

Code:

#!/bin/bash
mysql -e "USE DATABASE_NAME; LOAD DATA LOCAL INFILE '/Scripts/tableupload//20051101.cdr.txt' INTO TABLE ppscdr_200511 FIELDS TERMINATED BY '|';"

This assumes that the script is running as a user with permissions to DATABASE_NAME, of course.

Hope that helps.

maheshdf · 11-30-2006, 03:06 AM

Hi thanks for the help. It is working fine. I have another question.
How can I use a varible defined in the shell inside sql statement.

For example see the following, it dosent work,

Code:

#!/bin/bash

file_name = 20051101

mysql -e "USE DATABASE_NAME; LOAD DATA LOCAL INFILE '/Scripts/tableupload/$file_name.cdr.txt' INTO TABLE $file_name
FIELDS TERMINATED BY '|';"

Give me an idea to fix it.