Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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)
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial