+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Coders Lounge (https://hackforums.net/forumdisplay.php?fid=5)
+---- Thread: [DBMS] How can I insert thousands of rows to a database on multiple tables under these condi (/showthread.php?tid=4722666)
How can I insert thousands of rows to a database on multiple tables under these condi - zZeferiniXx - Mar 9, 2015
So what I want is to insert around 50,000 and possibly more records into the database but I have a relational database so I'm not sure how to handle this.
How I want the flow for each iteration:
Insert a student who's table structure is:
i60(.)tiny pic(.)com/34856ds.png
year_enrolled is randomized
status whether graduate/undergraduate is randomized
names are randomized
college_code is randomized(there's a table for this)
degree_code is randomized(there's a table for this)
After inserting the student,
I want to insert random values for each course on the student based on his degree, like let's say the student is taking up ComSci
i59(.)tiny pic(.)com/5e5tz4.png
I'd like to populate random values on the student's grade records
i57(.)tiny pic(.)com/2m6agkl.png
======
How can I achieve this?
* - stugz - Mar 9, 2015
Which database system are you using? PostgreSQL, SQL Server, Oracle, MySQL?
* - TryTo - Jul 5, 2015
simple novice way.
Dump that table into csv
open it in excel
change it up however you want
go to phpma
truncate table
upload csv.
* - stugz - Jul 6, 2015
Opening csv in Excel is a very bad idea. Please do not recommend this. It will silently chop off leading zeroes. This might have changed in versions of Excel > 2010 but I doubt it. CSV files should be opened in a decent text editor.
* - bakasura - Jul 8, 2015
Not really sure why is the discussion going towards CSV and excel. Not relevant here.
The solution to your problem is easy. Create a procedure with a cursor.
1. Let the cursor go through table with student names.
2. Use Order by RAND() to pick random college code and degree code. You can add WHERE to restrict it to stuff like ComSci
3. Once you have all the data do SELECT INTO or simple INSERT to push the data in the final table.
If you need explanation for any point, I'd be happy to answer.
Good luck