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.

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?