Hi @ahmed salah ,
Based on Viorel-1's answer, you could create a trigger or procedure to update the GeneratedId if newly created data is inserted tomorrow.
Please refer below example and check whether it is helpful:
--insert some new data
insert into #partswithcompany (SearchParts,CompanyId,GeneratedId)
values
('A5ghf7598fdmlcpghjk',1234,NULL),
('AKLJGSA75fukfgjpghjk',5870,NULL),
('A5ghfJKKJdfhgetrhsGghjk',818,NULL)
--update GeneratedId with same search parts and company id repeated
update a
set a.GeneratedId=b.GeneratedId
from #partswithcompany A
INNER JOIN #partswithcompany B ON A.SearchParts=B.SearchParts AND A.CompanyId = B.CompanyId
WHERE a.GeneratedId IS NULL and b.GeneratedId is not null
--update GeneratedId with different search parts and company id
; with M as
(
select a.*, dense_rank() over (order by a.SearchParts, a.CompanyId) as n
from #partswithcompany A
INNER JOIN #partswithcompany B ON A.SearchParts=B.SearchParts AND A.CompanyId = B.CompanyId
WHERE a.GeneratedId IS NULL and b.GeneratedId is null
)
update M
set GeneratedId = (select max(GeneratedId)+n from #partswithcompany where GeneratedId is not null)
select * from #partswithcompany
Output:
SearchParts CompanyId GeneratedId
A5ghf7598fdmlcpghjk 1234 2
AKLJGSA7598fdmlcpghjk 5870 4
A5ghfJKKJGHHGghjk 9081818 3
KHJLFFS8fdmlcpghjk 123345 5
A5ghf7598f7GGHYUTYA 3456 1
A5ghfJKKJGHHGghjk 9081818 3
A5ghf7598f7GGHYUTYA 3456 1
A5ghf7598f7GGHYUTYA 3456 1
A5ghf7598fdmlcpghjk 1234 2
AKLJGSA75fukfgjpghjk 5870 7
A5ghfJKKJdfhgetrhsGghjk 818 6
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.