Populating table in Mysql

Question

I'm trying to populate a table using different .txt files, but every time that I want to include or add a new file the previous files get over written, not sure why this is happening.

This how I built the table - I am presuming that is something wrong with the primary key

CREATE TABLE BONDS(
CUSIP varchar(15) NOT NULL,
Price decimal(8,2) NOT NULL,
Spot varchar(15),
Date_Priced varchar(55) not null,
Broker varchar(55) not null,
primary key(CUSIP)
);

This is how I import the .txt file

load data local infile 'c:/temp/test.txt'
into TABLE BONDS
FIELDS TERMINATED BY ';'

Thanks for the help

zxcyq · Answer 1 · 2020-04-13T04:06:20.817

0

Perhaps you should change your primary key column.

Your primary key should be an integer AUTO_INCREMENT'ed.

CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);

[1] More on AUTO_INCREMENT https://www.w3schools.com/sql/sql_autoincrement.asp

[2] More on LOAD DATA https://tecfa.unige.ch/guides/mysql/man/manuel_LOAD_DATA.html

edited Apr 13 '20 at 04:06

answered Apr 12 '20 at 18:30

zxcyq

1
2

I did, but I got the following error: Error Code: 1467. Failed to read auto-increment value from storage engine – Gabriel Apr 12 '20 at 18:32
What happens if you declare your Primary Key as an int instead? – zxcyq Apr 12 '20 at 18:42
According to the following, it could be, that you need to update the table. Declaring the Primary key as int assumes an empty table. The following link shows how to both update the table on the first answer and how to sort your current table contents as well [link](https://stackoverflow.com/questions/17690926/failed-to-read-auto-increment-value-from-storage-engine-error-number-1467/17690982) – zxcyq Apr 12 '20 at 18:43
Nop, it didnt work, this what I did: 1. I ran thisALTER TABLE bonds AUTO_INCREMENT =1; then I upload the the file, didnt work, 2. then I switch the code, didnt work, 3. then I I create an index : CREATE INDEX id ON bonds(id); ALTER TABLE bonds ORDER BY id ; then I upload the .txt file, it didnt work either. – Gabriel Apr 12 '20 at 19:14
It could be that the PK needs to be an integer. Because you are effectively trying to add an integer on a string. Do you need to keep the field a VARCHAR? Can you create the table from scratch or do you need to keep your values? – zxcyq Apr 12 '20 at 19:54
I would try something like: CREATE TABLE BONDS( CUSIP int NOT NULL AUTO_INCREMENT, Price decimal(8,2) NOT NULL, Spot varchar(15), Date_Priced varchar(55) not null, Broker varchar(55) not null, primary key(CUSIP) ); If that does not solve your problem, perhaps look into something like UPDATE tableName SET newCol = CAST(oldCol AS int); which is mentioned here [link](https://stackoverflow.com/a/18480609/13236364) But, in that case, you will need to take care of the existing values. – zxcyq Apr 12 '20 at 20:40

score 0 · Answer 2 · answered Apr 12 '20 at 20:43

Just restructure your table add a new column id int, drop the previous primary key constraint and add id as a primary key and then use the below Query

load data local infile 'c:/temp/test.txt'
into TABLE BONDS (@dummy, CUSIP,Price,Spot,Date_Priced,Broker)
FIELDS TERMINATED BY ';'

Populating table in Mysql

2 Answers2