Increment non unique field using a sql insert statement

I have three tables TelNo , RefNo and Ref_Check_No. RefNo and Ref_Check_No has columns Telephone_Id and Entity_Id respectively along with other columns, while TelNo has Telephone_Id and Entity_Id. What I would like to do is to insert all the records from Ref_Check_No into the RefNo table. The TelNo Table contains the Telephone_Id for each Entity_Id. All of these columns mentioned are not unique.

The thing is I know how to insert the data from the Ref_Check_No table. However, I can't seem to insert the Telephone_Id values for each Entity_Id. What I would like to do is to retrieve the MAX Telephone_Id number per Entity_Id from the TelNo table and increment it by 1 for each Entity_Id in the RefNo table using an insert statement. Below is an example of what I would like to achieve in the RefNo Table:

             **TelNo Table**                  **RefNo Table**
        Telephone_Id   Entity_Id        Telephone_Id    Entity_Id
             1         ABCD               4              ABCD
             2         ABCD               5              ABCD
             3         ABCD               6              ABCD
             89       EFGH               7              ABCD
             90          EFGH              96             EFGH
             95          EFGH              97             EFGH

Basically what I want to do is to find the MAX Telephone_Id number per Entity_Id (ABCD and EFGH) from the TelNo table and increment it by 1 for each corresponding Entity_Id in the RefNo table.

Below is the code that I was working on which I can't seem to get right.

开发者_运维知识库 INSERT INTO
RefNo
(
Telephone_Id,
Entity_ID,
ContactName
) 
SELECT     
  (SELECT COUNT(*) FROM Ref_Check_No b WHERE(a.Entity_Id = b.Entity_Id) 
    ISNULL ((SELECT MAX(Telephone_Id) AS Telephone_Id FROM TelNo 
    WHERE (Entity_Id = a.Entity_Id)), 0) AS Telephone_Id,

Entity_ID,
ContactName               
FROM         
Ref_Check_No

Is there a way to find the MAX Telephone_Id number and increment it by 1 for each Entity_Id using a correlated sub query while inserting data in the RefNo table at the same time?

---

I am having a hard time understanding your example. You want to create one row in RefNo for each record in Ref_Check_No, labelling each with the same Entity_ID and an increasing Telephone_ID?

If that is the case, try this (for MSSQL):

INSERT INTO RefNo (Entity_ID, Telephone_ID)    
SELECT r.Entity_ID, 
  t.MAXID + ROW_NUMBER() OVER(Partition BY r.Entity_ID order by r.Telephone_ID) AS Telephone_ID
FROM ref_check_no r
  INNER JOIN (
    SELECT Entity_ID, Max(Telephone_ID) as MaxID
    FROM TelNo 
      GROUP BY Entity_ID) t 
   ON r.Entity_ID=t.Entity_ID