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,BASTIMESTAM
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?
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,BASTIMESTAM
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?
MySQL Server
Last Comment
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.
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
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
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
Do you mind if I ask what purpose the colon serves in this case?
Many thanks,
Pete
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
It is an assignment variable causing the variable on the left to take on the value of what is on the right.
ASKER
Great, thanks again
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.