Inserting multiple records based on select and check records exists + Sql

Question

Hi I have created a sql query as seen below. Basically I need to achieve two things. 1. Insert record only if the correlationid doesnt exist in correlationmessage table. 2. Insert two records one with MessageTypeId = 1 and MessageTypeId = 2.

I have tried and achieved the second one but dont know how to do the first one

insert into MESSAGING.CorrelationMessage 
       (CorrelationId, MessageTypeId, RequestMessage,ResponseMessage, IsMatched, CreatedDate)
(
    Select ActivityID, 1 as MessageTypeId, NULL as RequestMessage, NULL as ResponseMessage, 1 as IsMatched, GETDATE() as CreatedDate  
    from account.Activity a
    join account.ActivityType b on b.ActivityTypeID=a.ActivityTypeID
    join ACCOUNT.TransactionType c on c.TransactionTypeID=a.TransactionTypeID
    where a.ActivityTypeID=3
    and ExecutingBroker is null
    and a.active=1 
    and TradeDate is null
    and DateforGTDOrders > GETDATE()

    union

    Select ActivityID, 2 as MessageTypeId, NULL as RequestMessage, NULL as ResponseMessage, 1 as IsMatched, GETDATE() as CreatedDate  
    from account.Activity a
    join account.ActivityType b on b.ActivityTypeID=a.ActivityTypeID
    join ACCOUNT.TransactionType c on c.TransactionTypeID=a.TransactionTypeID
    where a.ActivityTypeID=3
    and ExecutingBroker is null
    and a.active=1 
    and TradeDate is null
    and DateforGTDOrders > GETDATE()
)

This query is a bit different as it to doing multiple inserts based on select as also checking if the record exists. Since it is a union it is quite tricky to do the check.

If the duplicate did not help you reach an answer, can you edit your question to explain why the same technique cannot be used in your case? Why does a union make it tricky to check? — Tab Alleman, Oct 07 '15 at 15:28

score 0 · Accepted Answer · answered Oct 07 '15 at 15:05

With in your insert, you can:

select * from
(Select ActivityID,1 as MessageTypeId,NULL as RequestMessage  ,NULL as ResponseMessage,1 as IsMatched ,GETDATE() as CreatedDate  from account.Activity a
join account.ActivityType b on b.ActivityTypeID=a.ActivityTypeID
join ACCOUNT.TransactionType c on c.TransactionTypeID=a.TransactionTypeID
where a.ActivityTypeID=3
and ExecutingBroker is null
and a.active=1 
and TradeDate is null
and DateforGTDOrders > GETDATE()
union
Select ActivityID,2 as MessageTypeId,NULL as RequestMessage  ,NULL as ResponseMessage,1 as IsMatched ,GETDATE() as CreatedDate  from account.Activity a
join account.ActivityType b on b.ActivityTypeID=a.ActivityTypeID
join ACCOUNT.TransactionType c on c.TransactionTypeID=a.TransactionTypeID
where a.ActivityTypeID=3
and ExecutingBroker is null
and a.active=1 
and TradeDate is null
and DateforGTDOrders > GETDATE()) ab
where not exits ( select 1 from MESSAGING.CorrelationMessage a where a.CorrelationId = ab.CorrelationId and ab.MessageTypeId = a.MessageTypeId)

I have not tried this query but please give it a shot, I know this would not be most optimized way to do it. But, you would need to tune it. This is just an idea.

here is small correction to the where not exists clause. It is a.CorrelationId = ab.ActivityID . I get the following Error converting data type varchar to bigint. if i modify the query to where not exists ( select 1 from MESSAGING.CorrelationMessage a where (N'a.CorrelationId') = (N'ab.ActivityID')) . it shows me record in message table thought it exist — Tom, Oct 07 '15 at 15:23

Inserting multiple records based on select and check records exists + Sql

1 Answers1