Loading Legacy Data Into parent/Child Tables With Identities

It is one of those cases when OUTPUT clause comes very handy.

CREATE TABLE Staging(EmployeeCode CHAR(30), FullName VARCHAR(30),
PhoneNumber VARCHAR(30))
INSERT INTO Staging VALUES('ABC1234567890XYZ0987654321', 'Doe, John',
'(123)456-7890 home')
INSERT INTO Staging VALUES('ABC1234567890XYZ0987654321', 'Doe, John',
'(312)456-7890 cell')
INSERT INTO Staging VALUES('ABD123FHSDJKFH367FHASJ4321', 'Doe, Jane',
'(567)456-7891 home')
go

CREATE TABLE Party(PartyID INT IDENTITY PRIMARY KEY, EmployeeCode
CHAR(30), FullName VARCHAR(30))
CREATE TABLE Phone(PartyID INT NOT NULL, PhoneNumber VARCHAR(30))
go

DECLARE @InsertedIds TABLE(PartyID INT, EmployeeCode CHAR(30))


INSERT Party(EmployeeCode, FullName)
OUTPUT INSERTED.PartyID, INSERTED.EmployeeCode INTO @InsertedIds
SELECT DISTINCT EmployeeCode, FullName
FROM Staging


SELECT * FROM Party


PartyID EmployeeCode FullName
----------- ------------------------------
------------------------------
1 ABC1234567890XYZ0987654321 Doe, John
2 ABD123FHSDJKFH367FHASJ4321 Doe, Jane


SELECT PartyID, EmployeeCode FROM @InsertedIds


PartyID EmployeeCode
----------- ------------------------------
1 ABC1234567890XYZ0987654321
2 ABD123FHSDJKFH367FHASJ4321


INSERT Phone(PartyID, PhoneNumber)
SELECT
--(SELECT PartyID FROM @InsertedIds i WHERE Staging.EmployeeCode =
i.EmployeeCode)
PartyID,
PhoneNumber
FROM Staging JOIN @InsertedIds i ON Staging.EmployeeCode =
i.EmployeeCode


SELECT * FROM Phone


PartyID PhoneNumber
----------- ------------------------------
1 (123)456-7890 home
1 (312)456-7890 cell
2 (567)456-7891 home