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

closed as off-topic by LowlyDBA, Erik Darling, RDFozz, hot2use, Mike Walsh Oct 13 '17 at 1:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Erik Darling, RDFozz, hot2use, Mike Walsh
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    have you looked at - Extract data out of large tables into small tables with referential Integrity. 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 2: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
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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.