CODEWITHSUNDEEP
mysqllinuxbash
Code Geas Coder
Code Geas Coder

Reputation: 1979

Insert into mysql from linux terminal

I have a question. i can insert into mysql database from linux terminal a record with this command:

mysql dbTest insert into tablename values(1,"b","c")

Now i have a file in Linux with some records for example:

$cat file

2, "c", "e"
3, "r", "q"
4, "t", "w"
5, "y", "e"
6, "u", "r"
7, "g", "u"
8, "f", "j"
9, "v", "k"

i don't know how can i insert all records to the file to mysql database from linux terminal.

I intent with a bash file but i don't know =(

Upvotes: 3

Views: 24603

Answers (5)

slackmart
slackmart

Reputation: 4924

You can create a custom sql file using sed.

From a terminal execute the following code:

sed 's/\(^[0-9"]*, [a-z"]*, [a-z]*$\)/INSERT INTO tablename VALUES(\1);/g' source_file > sql_script.sql

After you can easily use the source command to insert the records.

$ mysql -u mysqlusername -p -h host
Enter password: your_secret_password

Mysql > use yourdatabasename
Mysql > source sql_script.sql

Upvotes: 1

Sumanta
Sumanta

Reputation: 1

  1. Install Mysql :

    sudo apt-get install mysql-server

  2. Goto Mysql shell:

    mysql -u root -p 
       password:******

  3. Show Database( if already exist):

    show databases;

  4. Create a databases:

    create database employees;

  5. Access the created database:

    use employees;</code>

  6. Create a table (Be careful on "``" and " " : use acutesign in each field):

    create table `employees`( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(64) DEFAULT NULL,`last_name` varchar(64) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `department` varchar(64) DEFAULT NULL, `salary` DECIMAL(10,2) DEFAULT NULL, PRIMARY KEY(`id`)) 
ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = latin1;

  7. Add Information to a table: 

    INSERT INTO `employees` (`id`,`first_name`,`last_name`,`email`,`department`,`salary`) 
VALUES (1, 'Sumanta','Silwal','[email protected]','Computer Design', '5000.00');

  8. Look the created tale:

    select * from employees;

Note: use "`" sign in each field like employees, id, first_name, last_name, email, department and salary.

**Enjoy coding.

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200193

Doing a series of inserts is not the best choice performance-wise. Since your input data exist as CSV you'd be better off doing a bulk load as @Kevin suggested:

mysql dbTest -e "LOAD DATA INFILE './file' INTO TABLE tablename FIELDS TERMINATED BY ','"

Upvotes: 3

K Adithyan
K Adithyan

Reputation: 396

mysql -e "use databasename;insert into TABLENAME values ('POST',........);"

Upvotes: 0

n3rV3
n3rV3

Reputation: 1106

Using awk you could use the existing file and insert values from bash itself:

awk 'BEGIN {print "mysql -u root -p dbTest << EOF"} {print "insert into tablename values (" $0 ");"} END {print "EOF"}' file |bash -v

Upvotes: 0

Related Questions