Sql-server – Inserting Data using Cursor but check if the data already in the table

cursorsinsertsql serverupdate

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

Best Answer

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
                        )
Related Question