Using SQL Server 2008R2.
I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the relationships?
For example, I have a table with columns such as: StudentFirstName, StudentLastName, TeacherName, Class1, class2, class3, class4, AdvisorName, etc.
I want to copy that to multiple tables, such as:
Student - StudentId, FirstName, LastName
Teacher - TeacherId, FirstName, LastName
StudentTeacher - Id, StudentId, TeacherId
When I insert the data into the Student and Teacher tables, I need to grab the ID fields from them (StudentId, TeacherId) and load those into the the StudentTeacher table, in the correct order.
Is this best done with SSIS? If so, will I need a bunch of data flows because of the "one source, one destination" thing?
If I do it with a script, I'm thinking the OUTPUT clause might help, but I have not yet figured out how to make that work.
I have tried this:
INSERT INTO Student (Name) OUTPUT Inserted.StudId INTO dbo.StudentTeacher (StudentId) VALUES ('Jimmy'), ('Bobby'), ('Nanacy'), ('Suzie') INSERT INTO Teacher(Name) OUTPUT Inserted.TeachId INTO dbo.StudentTeacher (TeacherId) VALUES ('Mr Jim'), ('Mr Bob'), ('Ms Nancy'), ('Ms Suzie')
But that doesn't load at the same time, so I get multiple rows, one with a studentid, one with a teacherid. How do I get them to load at the same time?
Just looking for some direction