CODEWITHSUNDEEP
sqlmysqlimport
mlzboy
mlzboy

Reputation: 14691

Importing CSV to MySQL without losing an auto-increasing field's data

I imported data from MySQL to a CSV. Now I am trying to import it into another machine's MySQL. But I want to preserve the original auto-increased id's values.

I tried this:

LOAD DATA INFILE '/tmp/region.csv'
INTO TABLE regions
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

(id,name,parent_id,level)

But this didn't work. Any help?

Upvotes: 2

Views: 3411

Answers (2)

Gav
Gav

Reputation: 11460

The "auto increase" field you speak of is "auto increment". To preserve it, temporarily alter the table to not use AUTO INCREMENT on the ID column, then once imported, alter it back.

E.g 

ALTER TABLE `table_name` CHANGE `id` `id` INT(11)  UNSIGNED  NOT NULL;

Then:

ALTER TABLE `table_name` CHANGE `id` `id` INT(11) UNSIGNED  NOT NULL  AUTO_INCREMENT  PRIMARY KEY;

Upvotes: 2

TDB
TDB

Reputation: 1

you can try to create the target table with a column 'id' without auto increment property, load the csv file, and then add auto increment attribute to the column.

Upvotes: 0

Related Questions