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
精彩评论