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.
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?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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)
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
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
It is an assignment variable causing the variable on the left to take on the value of what is on the right.
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.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Adva… 188 lessons
-
Microsoft ApplicationsCertification: MCSE Microsoft Certified Systems Engineer - Securing… 324 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.