CODEWITHSUNDEEP
mysqlsql
rochellecanale
rochellecanale

Reputation: 205

Insert new table information in an existing mysql table?

Hello guys just want to ask about mysqldump. How can i insert a csv values in an existing mysql table with no values inside? Because I have my .csv file that contains two columns. ID and NAME and I have a table that has 3 columns. One is the id which is auto increment the other is brgy_name and lastly is the brgy_code. In my csv the ID column values should be place in brgy_code and the NAME column values should place in the brgy_name. 

Example:

"id","name"
"1001","Bangued"
"1002","Boliney"
"1003","Bucay"
"1004","Bucloc"
"1005","Daguioman"
"1006","Danglas"
"1007","Dolores"
"1008","La Paz"
"1009","Lacub"
"1010","Lagangilang"
"1011","Lagayan"
"1012","Langiden"
"1013","Licuan-Baay"
"1014","Luba"
"1015","Malibcong"
"1016","Manabo"
"1017","Penarrubia"
"1018","Pidigan"
"1019","Pilar"
"1020","Sallapadan"
"1021","San Isidro"
"1022","San Juan"
"1023","San Quintin"
"1024","Tayum"
"1025","Tineg"
"1026","Tubo"
"1027","Villaviciosa"
"1028","Butuan City"
"1029","Buenavista"
"1030","Cabadbaran"
"1031","Carmen"
.
.
.

In my table there are no rows inside. Here's my table structure:

CREATE TABLE `ref_barangay` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `brgy_name` VARCHAR(50) NOT NULL,
    `brgy_code` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

My problem is how can i insert my csv file? In my csv file the column id is the brgy_code.

Here's the what i want to do:
id    brgy_name        brgy_code
1     Bangued          1001
2     Boliney          1002
.
.
.

That's all guys how can i do that? I want to it using mysql dump but how can i do?

Upvotes: 0

Views: 95

Answers (1)

peterm
peterm

Reputation: 92845

Try LOAD DATA INFILE

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE ref_barangay
    FIELDS TERMINATED BY ',' 
           OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\n' -- or \r\n
    IGNORE 1 LINES
(@code, @name)
 SET brgy_name = @name, brgy_code = @code

Output

mysql> select * from ref_barangay;
+----+--------------+-----------+
| id | brgy_name    | brgy_code |
+----+--------------+-----------+
|  1 | Bangued      | 1001      |
|  2 | Boliney      | 1002      |
|  3 | Bucay        | 1003      |
|  4 | Bucloc       | 1004      |
...

Upvotes: 1

Related Questions