How do I turn this statement into an IF NOT EXISTS statement? 1

[grippy]

I've been racking my brain on this and after several google searchs and attempts I can not get this to work. Basically the statement works fine but I want to run it daily and do not want it duplicating the results if they already exist in the table. Let me know if you guys can help me figure this one out. Thank you.

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)
SELECT PD.Id  AS ProductId, 
       sao.Id AS SpecificationAttributeOptionId, 
       '1'    AS AllowFiltering, 
       '0'    AS ShowOnProductPage, 
       sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
      
      select id, 
             name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then 0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            
            select id, 
                   name, 
                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax
            from (       
                 
                  select id, 
                         name, 
                         loc
                  from (
                        
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as PdRng
on PdRng.id = PD.id

join (
      
      select *
      from SpecificationAttributeOption 
      where isnumeric(Name) > 0
        and len(rtrim(Name)) = 4 AND SpecificationAttributeOption.SpecificationAttributeId = '7'
     ) as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax

 

[SQLScholar]

How about this (untested so please try on a dev box)

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)
SELECT PD.Id  AS ProductId, 
       sao.Id AS SpecificationAttributeOptionId, 
       '1'    AS AllowFiltering, 
       '0'    AS ShowOnProductPage, 
       sao.DisplayOrder AS DisplayOrder
FROM Product as PD
join (
      
      select id, 
             name, 
             ymin as pcmin,       
             case when ymax < ymin then 99 else ymax end as pcmax,
             case when ymax < ymin then 0 else ymin end as ccmin,
             ymax as ccmax
      from (       
            
            select id, 
                   name, 
                   convert(int, SUBSTRING(name, loc-2, 2)) as ymin,
                   convert(int, SUBSTRING(name, loc+1, 2)) as ymax
            from (       
                 
                  select id, 
                         name, 
                         loc
                  from (
                        
                        select id, 
                               name,
                               CHARINDEX('-', name) as loc
                        from PRODUCT
                        where CHARINDEX('-', name) > 0
                       ) as tbl
                  where SUBSTRING(name, loc-3, 1) = ' ' 
                    and SUBSTRING(name, loc+3, 1) = ' '
                 ) as rng
            where convert(int, SUBSTRING(name, loc-2,2)) > 0
              and convert(int, SUBSTRING(name, loc+1,2)) > 0
           ) as yrs
     ) as PdRng
on PdRng.id = PD.id

join (
      
      select *
      from SpecificationAttributeOption 
      where isnumeric(Name) > 0
        and len(rtrim(Name)) = 4 AND SpecificationAttributeOption.SpecificationAttributeId = '7'
     ) as sao       
ON convert(int, Right(sao.Name, 2)) between PdRng.pcmin and PdRng.pcmax
OR convert(int, Right(sao.Name, 2)) between PdRng.ccmin and PdRng.ccmax  

except
select
ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder
from
Product_SpecificationAttribute_Mapping

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[grippy]

right on it worked! Thanks a bunch for the help!

 

[grippy]

how would it work with this one? I tried adding the except clause to the bottom but SQL doesn't like it on this statement.

INSERT INTO Product_SpecificationAttribute_Mapping
(ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder)

SELECT Product.Id AS ProductId, SpecificationAttributeOption.Id AS SpecificationAttributeOptionId, '1' AS AllowFiltering, '0' AS ShowOnProductPage, '1' AS DisplayOrder
FROM Product
INNER JOIN SpecificationAttributeOption ON ' '+Product.FullDescription+' ' LIKE '% ' + SpecificationAttributeOption.Name +' %' 
WHERE SpecificationAttributeId = '5'
ORDER BY SpecificationAttributeOption.Name ASC

except
select ProductId, SpecificationAttributeOptionId, AllowFiltering, ShowOnProductPage, DisplayOrder
from
Product_SpecificationAttribute_Mapping

 

[SQLScholar]

Hi,

Its because of the order by. Do you need to order the insert? If so i can probably code round it.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------