SQLTeam.com | Weblogs | Forums

How to insert data


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

 TradeCodeId int identity(1,1),
 PartId  int,
 Partlevel int,
 CodeType  nvarchar(50),
 CodeValue nvarchar(50)
 insert into #TradeCode(PartId,Partlevel,CodeType,CodeValue)VALUES
Expected  data inserted to #tradeCode temp table as below :


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.

this doesn't make any sense. You are directly inserting records into the #TradeCode table, but then you state

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

How would it ever get in there? Are you sating that you want to use the CodeValue from #TradeCode as a lookup to the CodeValueFrom in #MappingCodeValue and then use the CodeValueTo from #MappingCodeValue to insert into #TradeCode??

Can you also provide an example of what it looks like when it already exists and what you expect?

this is solved by this query
insert into #TradeCode
select c.PartId, c.Partlevel, c.CodeType, m.CodeValueTo
from #MappingCodeValue as m
inner join #TradeCode as c on c.CodeType = m.CodeTypeFrom and c.CodeValue = m.CodeValueFrom
where not exists( select * from #TradeCode where CodeType = c.CodeType and CodeValue = m.CodeValueTo)
Select * from #TradeCode