Query to assign the same ID to row being inserted if it it already exists in the table

Question

I am inserting customer records into a table where, if a record with the same name already exists, I assign the same ID to the newly inserted record.

Assume table T has this record:

ID | Name | Phone_Number | Date_Inserted
105| Sam  | 111111       | 04/03/2014
106| Rita | 222222       |04/03/2014

And I'm inserting this from table A:

Name| Phone_Number
Sam | 333333

Then after insertion, table T should have:

ID | Name | Phone_Number | Date_Inserted
105| Sam  | 111111       | 04/03/2014
106| Rita | 222222       | 04/03/2014
105| Sam  | 333333       | 04/04/2014

Without the above change it would look like:

INSERT INTO T SELECT CustID.nextval,Name,Phone_Number,SYSDATE FROM A;

I was thinking of using,

INSERT INTO T
  SELECT CASE 
           WHEN NOT EXISTS(select null from T WHERE T.Name=A.Name) THEN CustID.nextVal
           ELSE (select ID from T where T.Name=A.Name) 
         END, 
         Name,
         Phone_Number,
         SYSDATE 
   FROM A;

But I'm not sure if it'll work and it seems redundant/bad for performance. If there's a preferred way to do this, please let me know.

Answer 1

If your schema is not set in stone, I would perhaps reconfigure it so that there is a "person" table and a separate "person phone number" table. With that sort of set up, you can associate multiple phone numbers with one person, and you won't be stomping on IDs, or creating confusing secondary ID columns that aren't primary keys.