Loading

How to add codevalueto when codevaluefrom equal codevalue #t

How to add codevalueto when codevaluefrom equal codevalue #t

ahmed elbarbary

6y657

problem

How to insert CodeValueTo on Table TradeCode where codevaluefrom equal to codevalue on table tradecode and not exist on Tradecode table ?

I need to insert CodeValueTo into temp table #tradecode where CodeValueFrom exist on temp table #tradecode

I work on SQL server 2012 and below is my sample data

create table #MappingCodeValue
(
id int identity (1,1),
CodeTypeFrom nvarchar(50),
CodeValueFrom nvarchar(50),
CodeTypeTo nvarchar(50),
CodeValueTo nvarchar(50)
)
INSERT INTO #MappingCodeValue
(CodeTypeFrom,CodeValueFrom,CodeTypeTo,CodeValueTo)
VALUES
('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),
('ECCS-US','AB756-US','ECCS-URB','AB778-URB')
CREATE TABLE #TradeCode
(
TradeCodeId int identity(1,1),
PartId int,
Partlevel int,
CodeType nvarchar(50),
CodeValue nvarchar(50)
)
insert into #TradeCode(PartId,Partlevel,CodeType,CodeValue)VALUES
(1222,1,'ECCS-US','AB123-US'),
(1255,1,'ECCS-US','AB555-US'),
(1444,1,'ECCS-US','AB666-US'),
(1931,1,'ECCS-US','AB756-US')
Expected data inserted to #tradeCode temp table as below :
(1222,1,'ECCS-US','AB123-URB'),
(1255,1,'ECCS-US','AB555-URB'),
(1444,1,'ECCS-US','AB666-URB'),
(1931,1,'ECCS-US','AB778-URB')

I check if value of codevaluefrom on temp table #mappingcodevalue exist on temp table #tradecode field codevalue

then get equal value from codevalueTo then add it to temp table #tradecode with code type

to summarize what i need is to check #tradecode temp table field codevalue if it have same value on codevaluefrom on temp table

#mappingcodevalue then get codevalueto and add it as new row with equation value codevalueto in case of not exist on table #tradecode.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar6y ago
This link might help you. https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/
0