Click here to Skip to main content
15,392,968 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on sql server 2017 I have table #partsfeature already exist as below

create table #partsfeature
  (
  PartId int,
  FeatureName varchar(300),
  FeatureValue varchar(300),
  TechnologyId int
  )
   insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
   values
   (1211,'AC','5V',1),
   (2421,'grail','51V',2),
   (6211,'compress','33v',3)


my issue Done For Part id 3900 it take wrong

Technology Id 7 and Correct Must be 2

Because Feature name and Feature Value Exist

So it Must Take Same TechnologyId Exist

on Table #partsfeature as Technology Id 2 .

correct will be as Below

+--------+--------------+---------------+-------------
   | PartID |  FeatureName |  FeatureValue | TechnologyId
   +--------+--------------+---------------+-------------
   |   3900 | grail        | 51V           |   2
   +--------+--------------+---------------+-------


Expected Result For Parts Inserted

PartId	FeatureName	FeatureValue	TechnologyId
7791	AC	59V	4
8321	Angit	50V	5
5442	compress	30v	6
3900	grail	51V	2


What I have tried:

 insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
select  PartId,FeatureName,FeatureValue,
        TechnologyId  = dense_rank() over (order by FeatureName,FeatureValue)
                      + (select max(TechnologyId) from #partsfeature)
from    
(
        values
        (3900,'grail','51V',NULL),
        (5442,'compress','30v',NULL),
        (7791,'AC','59V',NULL),
        (8321,'Angit','50V',NULL)
) s (PartId,FeatureName,FeatureValue,TechnologyId)
Posted
Updated 20-Jul-22 1:48am
Comments
Member 15627495 11-Jun-22 15:52pm
   
you calculate by "addition" with :
Technology = dense_rank over ..... + select max(technologyId)

to debug :
look up if the hazardous value change, if you reload your query multiple times.
if not , which calculation between fields give the wrong number ..


so rewrite your second part of request.
ahmed_sa 11-Jun-22 15:57pm
   
you calculate by "addition" with :
Technology = dense_rank over ..... + select max(technologyId)
yes
ahmed_sa 11-Jun-22 15:59pm
   
i can assign rank for parts based on feature name and feature value
issue i face if feature name and feature value exist before then take technology id for that

1 solution

All what you need to do is to join data on FeatureName column. See:

SQL
WITH CTE AS
(
  SELECT 3900 AS PartId, 'grail' AS FeatureName, '51V' AS FeatureValue
  UNION ALL
  SELECT 5442,'compress','30v'
  UNION ALL
  SELECT 7791,'AC','59V'
  UNION ALL
  SELECT 8321,'Angit','50V'
)
INSERT INTO partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
SELECT A.PartId, A.FeatureName, A.FeatureValue, B.TechnologyId
FROM CTE AS A INNER JOIN partsfeature AS B
  ON A.FeatureName = B.FeatureName;


SQL Server 2019 | db<>fiddle[^]

Result:
PartId 	FeatureName 	FeatureValue 	TechnologyId
1211 	AC 	 	 	 	5V 	 	 	 	1
2421 	grail 	 	 	51V 	 	 	2
6211 	compress 	 	33v 	 	 	3
3900 	grail 	 	 	51V 	 	 	2
5442 	compress 	 	30v 	 	 	3
7791 	AC 	 	 	 	59V 	 	 	1


Note: you have to handle the case when there's no corresponding data in partsfeature table. In other words, when TechnologyId returns NULL, the newly created data will not be inserted into partsfeature table, because of join type (INNER).
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900