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:

INTO TABLE bastestdomainimport_test

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?
Pete BradstreetAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim RiddlesPrepress/OMS SpecialistCommented:
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.
Pete BradstreetAuthor Commented:
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.
Jim RiddlesPrepress/OMS SpecialistCommented:
Here is something that might work...test first, of course.

SET @tmp_id = 1000000000;
INTO TABLE bastestdomainimport_test

IGNORE 1 ROWS (ID,BASVERSION,BASTIMESTAMP,TestNumber) SET ID = (@tmp_id := @tmp_id + 1)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Pete BradstreetAuthor 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,
Jim RiddlesPrepress/OMS SpecialistCommented:
It is an assignment variable causing the variable on the left to take on the value of what is on the right.
Pete BradstreetAuthor Commented:
Great, thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.