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
Pete Bradstreet used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim RiddlesPrepress/OMS Specialist

Commented:
This may seem overly simplistic, but why not add a field for ID in your CSV file with the values you want to import?
If your CSV file does not already have an ID column, you could create a script in your favorite language to parse the file and insert the ID programmatically.

Author

Commented:
Hi,
The example that I am working with is actually part of a bigger picture where I will be creating numerous records in numerous fields from a single complex spreadsheet using stored procedures triggered by our application. As such, a single id number in the spreadsheet, even if is was populated would not suffice. I need to be able to have the load in file sql script somehow populate the id numbers incrementally using a variable or other method. Please let me know if my response is unclear.
Prepress/OMS Specialist
Commented:
Here is something that might work...test first, of course.

SET @tmp_id = 1000000000;
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 = (@tmp_id := @tmp_id + 1)

Open in new window

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
Excellent! Thanks very much, that worked. I was playing around with something very similar to this earlier but was using '=' rather than ':='

Do you mind if I ask what purpose the colon serves in this case?

Many thanks,
Pete
Jim RiddlesPrepress/OMS Specialist

Commented:
It is an assignment variable causing the variable on the left to take on the value of what is on the right.

Author

Commented:
Great, thanks again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial