I have a database that I normalized to 3N. Now I have a large amount of data
from a 3rd party that is in a flat file that I wish to bring into the
normalized (but empty) database. If I simply needed to split the data up
into the appropriate tables, I can see to build multiple inserts using a
temp table. What I cannot figure out is how to create my 1-to-many
relationships as I do the inserts. I've read several posts on this topic,
but none that really give any specific guidance. Any advice?

OK so what I would do is this

Import the file into a working table of the same structure

Import the Parent attributes to the real table(s)
Import the child attributes. You will need to join back onto the working
table.

This will involve making multiple passes over the same data.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Earl" <brikshoe (AT) newsgroups (DOT) nospam> wrote

Thanks Allan, I will work with that.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks Allan. It sounds like you are saying to import to the Parent table,
creating an Identity primary key, then doing the Inserts back into the child
tables, using the PK as the FK for the child tables?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hi Earl,

suppose the flat table is following:

pk1 pa1 ck1 ca1
pk1 pa1 ck2 ca1
pk2 pa2 ck3 ca1
pk2 pa2 ck4 ca1

pk= parent table primary key
pa= parent table attribute
ck= child table primary key
ca=child table attribute

we may use the following method:

1. create temp table by select pk, pa columns

2. Select distinct to get the primary table

3. Select ck, ca, pk column into the child table directly. pk is foregin
key in the child table at this time.

Hope it helps.

Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!