CODEWITHSUNDEEP
sqlsql-server-2012
Rico Strydom
Rico Strydom

Reputation: 549

SQL Server insert into where not exists

I am trying to insert into a table values where the value don't already exist.

This is what I have at the moment:

insert into UToolDb.dbo.MQ_MESSAGES_NEW (ENVId, QueueName, CorrelationId, MessageId, MessageContent) 
   select 
      E.ENVId,
      'RO00.GAT1.STUB_OPVANG_1.AI',
      '323031333130303431313533343933383438363739353032',
      'C3E2D840E2D8F1F24040404040404040CC0F7930FE822942', 
      'TEST Content'
   from 
      UToolDb.dbo.ENVIRONMENTS E 
   where 
      E.EnvironmentName = 'KETEN1' 
      and not exists (select 
                         EE.ENVId, 
                         'RO00.GAT1.STUB_OPVANG_1.AI',
                         '323031333130303431313533343933383438363739353032',
                         'C3E2D840E2D8F1F24040404040404040CC0F7930FE822942', 
                         'TEST Content'
                      from 
                         UToolDb.dbo.ENVIRONMENTS EE 
                      where 
                         EE.EnvironmentName = 'KETEN1'      
                         and EE.EnvironmentName = E.EnvironmentName)

For some reason it does not insert:

 (0 row(s) affected).

The values being inserted do not exist in the table.

What am I doing wrong?

Upvotes: 0

Views: 529

Answers (2)

Rico Strydom
Rico Strydom

Reputation: 549

Found the solution:

insert into UToolDb.dbo.MQ_MESSAGES_NEW (
   ENVId,
   QueueName,
   CorrelationId,
   MessageId,
   MessageContent
) 
select 
   E.ENVId,
   ?,
   ?,
   ?, 
   ?
from UToolDb.dbo.ENVIRONMENTS E 
where E.EnvironmentName = 'KETEN1' 
and not exists(
   select 
      E.ENVId,
      M.*
   from UToolDb.dbo.ENVIRONMENTS E 
   left join UToolDb.dbo.MQ_MESSAGES_NEW M
   on E.ENVId = M.ENVId
   where E.EnvironmentName = 'KETEN1' 
   and QueueName = ?
   and CorrelationId = ?
   and MessageId = ?
   and MessageContent = ?
)

Upvotes: 0

radar
radar

Reputation: 13425

you are getting a row from ENVIRONMENTS and checking again if the same row not exists in the same table.

probably you wanted if row already exists in MQ_MESSAGES_NEW instead of ENVIRONMENTS

Upvotes: 2

Related Questions