customer_no  phone_no phone_type
111111       555-1212   1
111111       555-1213   3

Insert into T_Phone
(Customer_No,Phone_no,Phone_type)

Select Customer_No,Phone_no,Phone_type
from NCOA2 N
Where NOT Exists ( Select * from T_Phone Where Customer_no = N.Customer_No and Phone_Type = 2)

Update T
Set T.Phone_No = N.Phone_No
from T_Phone T
inner join NCOA2 N on N.CustomerNo = T.Customer_No and T.Phone_Type = N.Phone_Type and T.Phone_Type = 2

MERGE T_PHONE t
USING NCOA2 a
	ON t.customer_number = a.customer_number
		AND t.phone_type = 2
WHEN matched THEN
	UPDATE SET t.phone_no = a.phone_no
WHEN NOT matched BY TARGET THEN
	INSERT 
		(customer_no,phone_no,phone_type)
	VALUES
		(a.customer_no, a.phone_no, 2);

quote:

---

Originally posted by wandoctor

I knew I'd forget something, there is a column in T_PHONE called entry that is the PK. It needs to auto increment if there is an insert.

---

SELECT COLUMNPROPERTY(OBJECT_ID('T_PHONE'),'entry','IsIdentity')

DECLARE @nextId INT;
SELECT @nextId = COALESCE(MAX(entry), 0)
FROM   T_PHONE;

INSERT INTO T_Phone
  (
    Customer_No,
    Phone_no,
    Phone_type,
    entry
  )
SELECT Customer_No,
       Phone_no,
       Phone_type,
       @nextId + ROW_NUMBER() OVER(ORDER BY n.customer_id)
FROM   NCOA2 N
WHERE  NOT EXISTS (
           SELECT *
           FROM   T_Phone
           WHERE  Customer_no        = N.Customer_No
                  AND Phone_Type     = 2
       )