Inserting data into multiple tables

[edwilli]

I have two tables named "People" and "Email". Because people can have more than one email, I created a one-to-many relationship between these two tables with a PK/FK named PeopleID.

Now someone has given me a list of 1000 names and email addresses they want added to the database. The list is in the following format:

FirstName1, LastName1, Email1
FirstName2, LastName2, Email2
FirstName3, LastName3, Email3
etc...

How can I add the names to the "People" table and the emails to the "Email" table in one step; given that the PeopleID is generated only when a new record is inserted?

Any help is appreciated.

Eric

 

[edwilli]

Oh, and the solution cannot include temp tables. That's cheating!

 

[JohnDTampaBay]

I don't think you can do it in one step. You'll need at least one INSERT query for each table. How does this work for you? Good luck!

-- Only insert new people
INSERT INTO People (FirstName, LastName)
SELECT DISTINCT InputTable.FirstName, InputTable.LastName
FROM InputTable
    LEFT OUTER JOIN People
    ON InputTable.FirstName = People.FirstName
    AND InputTable.LastName = People.LastName
WHERE People.FirstName IS NULL

-- Insert new emails with PeopleID
INSERT INTO Email (PeopleID, Email)
SELECT People.PeopleID, InputTable.Email
FROM People
    INNER JOIN InputTable
    ON InputTable.FirstName = People.FirstName
    AND InputTable.LastName = People.LastName
AND NOT EXISTS(SELECT * FROM Email e2
               WHERE e2.PeopleID = People.PeopleID
               AND e2.Email = InputTable.Email)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)