Justin Justin - 6 months ago 8
SQL Question

SQL insert statement with "NOT EXIST" based on second column

I have this functioning insert statement, but I need to insert into SL_PROD only if the NUMBER does not already exist for DEPTCODE '725'... any ideas?

INSERT INTO SL_PROD (NUMBER, DEPTCODE, DISP_SEQ, LU_BY, LU_ON)
SELECT ST_EXTRA.NUMBER, '725', '1', 'IN', getdate()
FROM ST_EXTRA
INNER JOIN STOCK ON STOCK.NUMBER = ST_EXTRA.NUMBER
WHERE UNITS > 0 AND datename(m,colEMAILDATE) = datename(m, DATEADD(m, -1, getdate()))

vkp vkp
Answer

Use not exists.

INSERT INTO SL_PROD (NUMBER, DEPTCODE, DISP_SEQ, LU_BY, LU_ON)
SELECT ST_EXTRA.NUMBER, '725', '1', 'IN', getdate()
FROM ST_EXTRA
INNER JOIN STOCK ON STOCK.NUMBER = ST_EXTRA.NUMBER
WHERE UNITS > 0 
AND datename(m,colEMAILDATE) = datename(m, DATEADD(m, -1, getdate()))
AND NOT EXISTS (select 1 from sl_prod 
                where number = st_extra.number
                and deptcode = '725')
Comments