Link to home
Create AccountLog in
Avatar of Pete Bradstreet
Pete BradstreetFlag for Canada

asked on

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?
Avatar of Jim Riddles
Jim Riddles
Flag of United States of America image

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.
Avatar of Pete Bradstreet

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Jim Riddles
Jim Riddles
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
It is an assignment variable causing the variable on the left to take on the value of what is on the right.
Great, thanks again