We help IT Professionals succeed at work.
Get Started

Loading a file into mysql where the unique ID needs to be incremented during import and auto-incrementing the ID field is not an option in database.

Pete Bradstreet
on
102 Views
Last Modified: 2015-06-12
Hi, I have a situation where I am working with a web application that basically controls all aspects of the mysql database management. All tables, fields etc. are created and managed by the core application.

The core application is rules/object oriented application and I am trying to find a way to import large numbers of records directly into the database from outside the application in order to achieve high import speeds.

In the database, I probably have over 100 tables, each with a field titled "ID" which is the primary field, Not Null, but not auto incremented. There is a database table called "bas_idgen" which has a single field in it called "MAX_ID". The web application uses this MAX_ID value each time it creates a new record in any of the tables as the "ID" and then increments the MAX_ID value ready for the next record to be created. The current value of MAX_ID is somewhere around 10,000,000.

My challenge is that I am trying to import records into specific tables, all of which have this "ID" field and the ID column is not defined in the import spreadsheet, I cannot leave the "ID" blank during the import and I cannot make it auto-incremented in the database because the system maintains this field.

My thought was to create a second table similar to the bas_idgen which would have a single NextID field that I would use in a similar way but the numbering system would start at 1 billion which would leave me 990 million system generated ids before I had to worry about duplicate ids.

In my query, I was thinking about something like:

LOAD DATA INFILE 'e:/IMPORT.csv'
INTO TABLE bastestdomainimport_test

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (ID,BASVERSION,BASTIMESTAMP,TestNumber) SET ID = SELECT MAX NextID+1 FROM bastestdomainimport_nextsqlid

I realize that the "SET ID = " string above is non-sensical but I provide it as an indication of my thought process. Another thought that I had was to set a variable and then increment that variable but I am unclear how to execute this.

Does anyone have any ideas on the best way to approach this and if so, what would the correct syntax be as I am something of a neophyte in this area?
Comment
Watch Question
Prepress/OMS Specialist
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE