I work on sql server 2017 I have table #partsfeature already exist as below
sql server 2017
#partsfeature
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
Part id 3900
Technology Id 7 and Correct Must be 2
Because Feature name and Feature Value Exist
So it Must Take Same TechnologyId Exist
Take Same TechnologyId
on Table #partsfeature as Technology Id 2 .
as Technology Id 2
correct will be as Below
+--------+--------------+---------------+------------- | PartID | FeatureName | FeatureValue | TechnologyId +--------+--------------+---------------+------------- | 3900 | grail | 51V | 2 +--------+--------------+---------------+-------
what I try is
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)
Expected Result
PartId FeatureName FeatureValue TechnologyId 7791 AC 59V 4 8321 Angit 50V 5 5442 compress 30v 6 3900 grail 51V 2