2

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

etc.

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

BattlFrog
  • 183
  • 3
  • 9

1 Answers1

0

output alone is not going to work (easily) as you have two things going on 

insert into student (name) 
select distinct studentName  
  from table; 

insert into teacher (name) 
select distinct teacherName  
  from table; 

insert into studentTeacher(studentID, teacherID) 
select student.ID, teacher.ID 
  from table 
  join student 
    on student.name = table.studentName  
  join teacher 
    on teacher.name = table.teacherName;
paparazzo
  • 5,048
  • 1
  • 19
  • 32