This solution applies when you have repeated data in the CSV and you want to control what to do in every case. Since, there is nothing in your question about the following fields, I'm not including any logic for them:
customer_id INT,
in_use BIT
0 - create the tables you already declared in your question
1 - create a t_Bulk table having the exact structure of your cvs and execute the bulk copy
CREATE TABLE T_BULK (
Customer_Name VARCHAR(100),
Contact_First_Name VARCHAR(50),
Contact_Last_Name VARCHAR(50),
Phone1 VARCHAR(15),
Phone2 VARCHAR(15),
Fax VARCHAR(15)
);
GO
2 - With the T_BULK table already full, let's process each record:
DECLARE @ContactId int, @FirstName varchar(50), @LastName varchar(50), @Phone1 varchar(50), @Phone2 varchar(50), @Fax varchar(50)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT Contact_First_Name, Contact_Last_Name, Phone1, Phone2, Fax
FROM dbo.T_BULK
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
WHILE @@FETCH_STATUS = 0
BEGIN
//if the contact already exists we get the identity
IF EXISTS (select contact_id from customer_contact where first_name=@FirstName and last_name=@LastName)
select @ContactId = contact_id from customer_contact where first_name=@FirstName and last_name=@LastName
//if the contact does not exit we need to insert and retrieve the new Identity value
ELSE
BEGIN
insert customer_contact (first_name,last_name) values (@FirstName,@LastName)
set @ContactId = SCOPE_IDENTITY()
END
//now we continue with the phones and fax
//insert only new phones
INSERT contact_phone (contact_id,phone_num)
SELECT Id, Phone
FROM (select @ContactId as Id, @Phone1 as Phone UNION select @ContactId ,@Phone2) P
WHERE NOT EXISTS (SELECT * FROM contact_phone C WHERE C.contact_id = P.Id and C.phone_num = P.Phone);
//insert only new fax
INSERT contact_fax (contact_id,fax_num)
SELECT Id, fax
FROM (select @ContactId as Id, @Fax as fax) F
WHERE NOT EXISTS (SELECT * FROM contact_fax C WHERE C.contact_id = F.Id and C.fax_num = F.fax);
FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
Good luck
In order to include an additional field:
in the declare zone include an aditional variable:
@Extension.
which you must include in both FETCH NEXT lines. Then you need to include that variable when inserting the phones, but in this case you need to check that phone2 is not equeal to phone1, just to ensure you don't have repeated phones:
(select @contactId as Id, @Phone1 as Phone, @Extension as Extension
UNION select @ContactId, @Phone2, null where @Phone2 <>@Phone1) P
