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
  • 173
  • 2
  • 9
  • 1
    have you looked at - [Extract data out of large tables into small tables with referential Integrity](http://gallery.technet.microsoft.com/scriptcenter/97e560ae-42f3-45dc-8228-e25359065bd1). Might be useful. Also, for identity insert, you can turn OFF identity insert .. that will allow the identity values get inserted explicitly. – Kin Shah Feb 22 '16 at 23:58
  • 1
    @Kin and BattlFrog: for lookup tables such as `StudentType`, those shouldn't have `IDENTITY` fields in the first place. Those values should be set explicitly and should match the enums used in the app code to refer to the same things. `IDENTITY` and Sequences are for incoming user data, whereas lookup tables are system / application data and should always be known ahead of time. The `StudentType` table (and any other lookups) should be populated before migrating the rest of the data. – Solomon Rutzky Feb 23 '16 at 02:34
  • Ignoring the identity fields for a minute (I see your point and will take them out), Please see the edits regarding using the OUTPUT clause and let me know. – BattlFrog Feb 23 '16 at 16:19

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
  • 4,957
  • 1
  • 15
  • 31