How to bulk insert data into two SQL tables

Question

I have a task to read a large file and insert the data contained into a SQL database. In the "real" task, there are 10s of columns in several tables, but for the sake of discussion, I will use a simpler schema.

The data looks like this: FName, LName, Address.

Therefore, I declare a SQL type BulkInsertData to hold these three fields so that they can be passed in as a table-valued parameter.

Here are my tables:

dbo.Person
---+-------+-------+----------+
Id | Fname | Lname | AddressId|
---+-------+-------+----------+
 1 | Bob   | Smith |      42  |
 2 | Sue   | Baker |     234  |
---+-------+-------+----------+

dbo.Address
----+----------------------+
 Id | Address              |
----+----------------------+
 42 | 1600 Pennsylvania Ave|
234 | 10 Downing St        |
----+----------------------+

My goal is to insert the first and last names into the dbo.Person table and the address in the dbo.Address table, along with the proper foreign keys. Inserting the data into multiple tables is easy, but I'm stuck on the foreign key relationship.

If I were inserting only a single record, I would probably insert the address into the dbo.Address table and then use SELECT @@IDENTITY to get the ID of the new address to be inserted into dbo.Person along with the first and last name.

Here, Does SQL Server have something like @@IDENTITY that returns multiple values?, another Stacker explained to me that the INSERT statement works with the OUTPUT statement to save information about multiple inserted rows.

I think that what I would like to do would be to have a temporary table that would hold not only the data passed in, but also the primary and foreign keys associated with each person: a table that would hold Fname, Lname, Address, PersonId, and AddressId.

However, as far as I know, I can use the OUTPUT keyword to create new rows in a temp table, but I don't know how to use it to modify existing rows.

I have the feeling that this task should not be difficult, so how do I do this?

My SQL server is MS SQL Server 2008.

If I take the data below...

Adam, Dumas, 300 Broadway
Greg, Ho, 213 Main St

and bulk insert it into the tables I described above, the result should look like this:

dbo.Person
---+-------+-------+----------+
Id | Fname | Lname | AddressId|
---+-------+-------+----------+
 1 | Bob   | Smith |      42  |
 2 | Sue   | Baker |     234  |
 3 | Adam  | Dumas |     501  |
 4 | Greg  | Ho    |     502  |
---+-------+-------+----------+

dbo.Address
----+----------------------+
 Id | Address              |
----+----------------------+
 42 | 1600 Pennsylvania Ave|
234 | 10 Downing St        |
501 | 300 Broadway         |
502 | 213 Main St          |
----+----------------------+

Answer 1

CREATE TABLE TVP_ADDRESS
(
 ID INT IDENTITY(1,1) PRIMARY KEY,ADDRESS VARCHAR(100)
)

CREATE TABLE TVP_PERSON
(
 ID INT IDENTITY(1,1) PRIMARY KEY,FNAME VARCHAR(100),LNAME VARCHAR(100),ADDRESS_ID INT,CONSTRAINT fk_address_id_tvp_address FOREIGN KEY(address_id) REFERENCES tvp_address(id) ON DELETE CASCADE
)

CREATE TYPE TVPDATA
AS 
TABLE
(
 FNAME VARCHAR(100),LNAME VARCHAR(100),ADDRESS VARCHAR(100)
)
GO
CREATE PROCEDURE inser_tvpdata
(
 @TVP  TVPDATA READONLY
)
AS 
BEGIN
    DECLARE @T TABLE(ID INT,ADDRESS VARCHAR(100))

--first insert address get ids

    INSERT INTO TVP_ADDRESS(address) OUTPUT Inserted.* INTO @T
    SELECT DISTINCT
     t.address
    FROM @TVP t --LEFT OUTER JOIN TVP_address td ON t.address = td.address
    --WHERE td.address IS NULL 
    -- insert persons and get new ids 

    INSERT INTO tvp_person(Fname,lname,address_id)
    SELECT t.fname,t.lname,temp.id
    FROM @TVP t
    INNER JOIN @T temp ON temp.ADDRESS=t.address
END
GO
DECLARE @t  TVPDATA

INSERT INTO @t(fname,lname,address) VALUES ('john','r','test'),('steve','r','test2')

EXEC inser_tvpdata @t

SELECT *
FROM TVP_ADDRESS

SELECT *
FROM TVP_PERSON