When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?

All we need is an easy explanation of the problem, so here it is.

I work on SQL server 2017 I have table #Pressfeature as below

create table #Pressfeature
  (
  PartId int,
  PressName varchar(300),
  TechId int
  )
   insert into #Pressfeature(PartId,PressName,TechId)
   values
   (1211,'AC',1),
   (2421,'grail',2),
   (6211,'compress',3)

my issue Done For Part id 3900 it take wrong

TechId 5 and Correct Must be 2 Because Press Name Exist as Press Name grail.

If Press Name Not Exist it will Take Maximum Number + 1 meaning 4 as Press Name Angit

   +--------+--------------+---------------+-------------
    | PartID |  PressName   |   TechId  
    +--------+--------------+---------------+-------------
    |   3900 | grail        |   2 (Exist Before )
    +--------+--------------+---------------+-------

   +--------+--------------+---------------+-------------
    | PartID |  PressName   |   TechId  
    +--------+--------------+---------------+-------------
    |   8321 | Angit        |   4 (New and Not Exist)
    +--------+--------------+---------------+-------

what I try is

 insert into #Pressfeature(PartId,PressName,TechId)
select  PartId,PressName,
        TechId  =  dense_rank() over (order by PressName)
                      + (select max(TechId) from #Pressfeature)
from    
(
        values
        (3900,'grail',NULL),
        (8321,'Angit',NULL)
) s (PartId,PressName,TechId)

Expected Result After Insert Data

PartId FeatureName TechnologyId
3900 grail         2
8321 Angit         4

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If I understood you correctly, you want to keep the TechId for different PartIds with an existing PressName.
If that is what you want, I think you’re missing a reference to the original table – the way the query is written, it will only calculate the new TechId rather than reuse the existing one. To do that, you can LEFT JOIN to the original table on the PressName and then COALESCE the inserted TechId.

Like this:

select  s.PartId,s.PressName,
        TechId = COALESCE
        (
           pf.TechId,
           dense_rank() over (order by s.PressName) + (select max(TechId) from #Pressfeature)
        )
from    
(
        values
        (3900,'grail',NULL),
        (8321,'Angit',NULL)
) s (PartId,PressName,TechId)
LEFT JOIN #Pressfeature pf ON pf.PressName = s.PressName;

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply