SQL Query to copy data from one table into multiple other tables

Question

I'm looking to take one table with information in it, and copy that information into multiple other tables.

For example, I'll use a school. The school has a table with a roster of all students, their student ID, when they started school, when the school year ends, their grades for each class, their address, phone number, etc. All of this is in one table called School.AllStudentInfo.

What I want to do, is get all of that data, and then fill a few other tables with that information, such as:

• School.Students - This will be filled with all non-duplicate student IDs from the original School.AllStudentInfo, as well as specific info (home address, age, grade, etc).

• School.Classes - I want to fill this with all class Ids that were listed in the School.AllStudentInfo table (ignoring duplicates).

• Classes.Students - I want to fill this "bridge/link table" with a ClassId (Classes.Students.ClassId) that will have a relationship with the PK for School.Classes (School.Classes.Id). It will also have a relationship with a StudentId (Classes.Students.StudentId) that is related to the Schools.Students.Id column.

However, I do not know how to update multiple tables and multiple columns at a time, and have not found if this is actually possible to do, yet.

Is it? If so, how can it be done?

Table columns are thus:

School.Students
---------------
StudentId (int)

School.Classes
---------------
ClassId (int)

Classes.Students
---------------
ID (int)
Student (int)
Class (int)

Answer 1

You have to write multiple statements, you can't address multiple tables in one statement. Good news is, it's still no big deal.

First fill students and classes tables. Something like

INSERT INTO students
SELECT DISTINCT student_name, date_of_birth, whatever
FROM your_huge_denormalized_table;

Same story for classes table. Use auto_increment columns (as primary keys) to get your ID columns.

On a sidenote, don't use tablenames with a dot in the name. Usually it's like databasename.tablename. You get the idea...

Now that you have your two tables filled, fill the table that resolves the m:n relationship. Oh, here you won't need an ID column, just make the primary key (student, class).

INSERT INTO classes_students
SELECT DISTINCT s.ID, c.ID
FROM your_huge_denormalized_table d
INNER JOIN students s ON d.StudentName = s.StudentName AND d.dob = s.dob /*add as much columns as needed to really identify each student*/
INNER JOIN classes c ON d.class_name = c.class_name;

and that's it.