CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin
Justin

Reputation: 269

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()))

Upvotes: 0

Views: 53

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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')

Upvotes: 1

Related Questions