Insert values to a table from another table based on condition

Question

So I have a SQL table in which I am entering values by joining other columns and entering the column values from that table.

CREATE TABLE #temp_t (id INT, cname NVARCHAR(100), val INT, aid INT)
INSERT INTO #temp_t 
SELECT DISTINCT
ISNULL(IDXC.id, 0) id, sg.name + '-webApp' cName, 0 val, ag.ID aid
FROM spgroup sg
JOIN APPA APP ON sg.id > 1 AND sg.val & 4 = 0 AND APP.dagi = sg.id 
JOIN AIDBI XDI ON APP.bs = XDI.bsid
LEFT JOIN #IDXC ON IDXC.agpv = sg.id
WHERE IDXC.id IS NULL

Now while inserting values to the table I need to check if sg.name exists in sysName table if yes then -webApp needs to be replaced by -andApp otherwise it remains -webApp

How can I do the same?

Answer 1

You can use EXISTS in a CASE expression:

SELECT DISTINCT COALESCE(i.id, 0) as id,
       (sg.name +
        (CASE WHEN EXISTS (SELECT 1 FROM sysname sn WHERE sn.name = sg.name)
              THEN '-andApp' ELSE '-webApp'
         END)
       )  as cName,
       0 as val, ag.ID as aid
FROM spgroup sg JOIN
      APPA APP 
      ON sg.id > 1 AND (sg.val & 4) = 0 AND APP.dagi = sg.id  JOIN
      AIDBI XDI 
      ON APP.bs = XDI.bsid LEFT JOIN
      #IDXC i
      ON i.IDXCsgpv = sg.id
WHERE i.id IS NULL