How to add mapped to in table trade codes where mapped from exist?

Transact-SQL
,
1

I work on SQL server 2012 I face issue I can't insert mapped to into table trade codes where mapped from exist
on table trade code

so

firstly I will get MapCodeTypeFrom and MapCodeValueFrom from table settings

second i will search on table tradecodes for MapCodeTypeFrom and MapCodeValueFrom

if MapCodeTypeFrom and MapCodeValueFrom exist on table trade Codes

then add MapCodeTypeTo and MapCodeValueTo for every part

that meaning every part have MapCodeTypeFrom and MapCodeValueFrom on table TradeCodes

must have MapCodeTypeTo and MapCodeValueTo

with another meaning every part must have two rows

first row for MapCodeTypeFrom and MapCodeValueFrom

second row for MapCodeTypeTo and MapCodeValueTo

 create table #settings
 (
 MapId int,
 MapCodeTypeFrom nvarchar(50),
 MapCodeValueFrom int,
 MapCodeTypeTo nvarchar(50),
 MapCodeValueTo int
 )
 insert into #settings(MapId,MapCodeTypeFrom,MapCodeValueFrom,MapCodeTypeTo,MapCodeValueTo)
 values
 (1222,'ECCN',9910,'HTS',9920),
 (1222,'Hom',9950,'VOM',9960)
    

 create table #TradeCodes
 (
 PartId  int,
 CodeType nvarchar(50),
 Code   int,
 )
 insert into #TradeCodes(PartId,CodeType,Code)
 values
 (1334,'ECCN',9910),
 (1971,'ECCN',9910),
 (2050,'ECCN',9910),
 (3000,'VOM',9950),
 (3600,'VOM',9950),
 (3700,'VOM',9950)

final rows must added on table trade codes that represent mapped to is :slight_smile:

PartId CodeType Code
1334 HTS 9920
1971 HTS 9920
2050 HTS 9920
3000 VOM 9960
3600 VOM 9960
3700 VOM 9960

mapped to will added in case of mapped from code type and value Exist
on table trade codes

2

your results don't match the data since the CodeType = VOM <> MapCodeValueFrom = hom, but this is what I have

Select t.PartID, s.MapCodeTypeTo, s.MapCodeValueTo
from #TradeCodes t
join #settings s
on t.CodeType = s.MapCodeTypeFrom
and t.Code = s.MapCodeValueFrom

3

thank you very much for solving