Change column of a table to be AUTO_INCREMENT

Question

Change column of a table to be AUTO_INCREMENT

Navigation

#1 by (0 votes)

0

I have a table with an Id and a name. I am trying to populate it in the following way:

SET FOREIGN_KEY_CHECKS = 0;
load data infile'C:LarutademiCSV.csv'
into table t1
fields terminated by ';'
lines terminated by '\n'
IGNORE 1 lines
(idt1, nombreT1)
SET FOREIGN_KEY_CHECKS=1;

But there are 100 files of 1'000,000 records and each file has the id defined from 1 to 1'000,000, so when I insert it, it marks me an error of duplicate keys. How can I change from the statement that the id field is auto_increment?

mysql

asked by Rafael Pérez Redondo 18.02.2018 в 17:47

source

1 answer

Authentication recommendations in Angular 2+ The results are not shown in the iframe

score 0 · Answer 1

I would recommend that you first create the table with the idT1 field as AUTO_INCREMENT:

CREATE TABLE 'tableT1' (
   'idT1' int(11) NOT NULL AUTO_INCREMENT,
   'nombreT1' varchar(30),
   PRIMARY KEY ('idT1 ')
);

or, if you have already created it, update it:

ALTER TABLE 'tableT1' ADD 'idT1' INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

And then upload the file to your CSV:

LOAD DATA
  INFILE 'C:LarutademiCSV.csv'
  INTO TABLE tableT1
  COLUMNS TERMINATED BY '","'  
  LINES TERMINATED BY '\r\n' ;