1

I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, If it does EXIST jump that row (Avoid the insert) and continue to FETCH the next row.

What is the best way to do this? Is this even possible with a cursor?

Thanks everyone for the rapid response. Just to add some meat to the bone. I am querying customer data to send retention emails to customers that have been inactive for a certain number of days. For example customers that haven't purchased anything for 30 days since their first purchase will receive an email just to check up on them.

Once I have this data, I'm passing it to a Stored Procedure (don't have control over this SP) that requires it to be in a certain format and from there an email is sent out. I also have a log table that tracks all the emails data that I send to this SP. I want to check this log table for the customerID and the email template that I have sent to the SP in the past to avoid sending the same email template twice. Because I have to send the data in a certain format to the SP, I am forced to use a cursor so that I can format each row before I send it out. So I want to check the data at that point where am formatting it.

Snippet of the script:

OPEN CURSOR_CustomerID;
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MessageDetails ='   
    [ {"Key":"<*CustomerID*>","Value":"'+LTRIM(@CustomerIDr)+'"},   
    {"Key":"<*Name*>","Value":"'+RTRIM(@Name)+'"},   
    {"Key":"<*OrderNumber*>","Value":"'+RTRIM(@OrderNumber)+'"}   ]'
EXEC SendAnEMail @Email,NULL,NULL,@language,@FromEmail,@Templatetouse,@CustomerID@MessageDetails

Exec Insert_Email_Log @PCustomerID, @Name, @Email, @Templatetouse
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
END
CURSOR_CustomerID;
DEALLOCATE CURSOR_CustomerID;
END
  • 1
    Of course you can, but maybe you don't need a cursor for that. What is exactly the problem you're trying to solve? Because it can be done using a single UPDATE statement. – McNets Nov 17 at 16:28
  • I agree with @McNets, you should avoid cursors as much as possible in SQL. Think batch not row by row if you want to have great performance in SQL. Please give more detail about the all thing so that we can figure out what you are trying to do. – Dominique Boucher Nov 17 at 19:14
2

Is there a specific business reason you must insert each row one by one? Would something like this work?

INSERT INTO SomeTable
(
    Column1
    ,Column2
    ,Column3
)
SELECT  Column1
        ,Column2
        ,Column3
FROM    SomeOtherTable t
WHERE   1 = 1
        AND NOT EXISTS (
                            SELECT  1
                            FROM    SomeTable t2
                            WHERE   1 = 1
                                    AND t.Column1 = t2.Column1
                                    AND t.Column2 = t2.Column2
                                    AND t.Column3 = t2.Column3
                        )
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.