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

  • Question

  • User696604810 posted

    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.

    Friday, April 10, 2020 1:48 AM

Answers

  • User452040443 posted

    Try:

    insert into #TradeCode (PartId, Partlevel, CodeType, CodeValue)
         select t.PartId, t.PartLevel, t.CodeType, m.CodeValueTo
         from #TradeCode as t
         inner join #MappingCodeValue as m
             on m.CodeValueFrom = t.CodeValue
         where not exists (select 1 from #TradeCode as s
                           where s.CodeValue = m.CodeValueTo)
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2020 8:30 PM

All replies

  • User-474980206 posted

    trivial select

    select PartId,Partlevel,CodeType,m.CodeValueTo as CodeValue
    from #TradeCode c
    join #MappingCodeValue M
       on m.CodeValueFrom = c.CodeValue
    
    
    

    you will need 3 tables 

    the mapping table
    an import table
    and the final table

    so its:

    /*
    drop table #MappingCodeValue
    drop table #TradeCode
    */
    
    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)
     )
    
     CREATE TABLE #TradeCodeImport
     (
     TradeCodeId int identity(1,1),
     PartId  int,
     Partlevel int,
     CodeType  nvarchar(50),
     CodeValue nvarchar(50)
     )
     
     insert into #TradeCodeImport(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')
    
    insert into #TradeCode
    select PartId,Partlevel,CodeType,m.CodeValueTo as CodeValue
    from #TradeCodeImport c
    join #MappingCodeValue M
       on m.CodeValueFrom = c.CodeValue
    
    select *
    from #TradeCode
    
    

    Friday, April 10, 2020 6:58 PM
  • User696604810 posted

    thank you for reply

    there are some wrong on code above

    insert is correct 

    but when run script to second time it insert again and this is wrong

    because if codevalueto exist on temp table #tradecode then   it must Not add it again

    it must when insert one time when run again not insert 4 records again

    only one time

    so how to solve issue

    Friday, April 10, 2020 7:37 PM
  • User452040443 posted

    Try:

    insert into #TradeCode (PartId, Partlevel, CodeType, CodeValue)
         select t.PartId, t.PartLevel, t.CodeType, m.CodeValueTo
         from #TradeCode as t
         inner join #MappingCodeValue as m
             on m.CodeValueFrom = t.CodeValue
         where not exists (select 1 from #TradeCode as s
                           where s.CodeValue = m.CodeValueTo)
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2020 8:30 PM