I have an Excel file that has the following data.

name, address, city, state, zip, organization, oaddess,
ostate, ozip, pname, paddress, pcity, pstate,pzip

O = Organization
P = Parent

Now there are about 2000 records in that Excel file that
needs to be exported into 3 tables (Student, Organization,
Parent)

Student table has StudentID which is primary key.
Organization has OrganizationID and StudentID. Parent
table has parent table, but there is a table
(StudentParent) that has ParentID, StudentID.

How can I export those records ensuring that each table
includes the studentID which will be created from the
insertion? StudentID is an Identity column. I need to be
able to tell which Organization and Parents belong to that
student, Using DTS. Is that possible and what is the best
way to achieve that conclusion?

Shawn

You cold do this with a single DataPump, where the destination is either the
Organization or Parent table. The other two tables you would insert through
a lookup query. This would be held together through a single ActiveX
Transform. However I think it would be easier to import the data into a
single table in SQL, and then use a normal TSQL query to do the
transformation.


--
Darren Green
http://www.sqldts.com

"Shawn" <anonymous (AT) discussions (DOT) microsoft.com> wrote