Turn off implicit transactions

Question

Hello everyone, I would like to do a insert into using a select, but I know that some rows might fail (that is expected). Is there a way to change the implicit transactions of SQL Server 2008 off so that the ones that have not failed are not rolled back?

-- get the count of the customers to send the sms to
SELECT @count = COUNT(*)
FROM PreCampaignCustomer
WHERE Processed = 0 AND CampaignID = @campaignid 
AND ErrorCount < 5

WHILE (@count > 0)
BEGIN
    DECLARE @customerid INT,
            @carrierid INT,
            @merchantcustomerid INT,
            @smsnumber NVARCHAR(50),
            @couponcode NVARCHAR(20)

    SELECT TOP 1 @customerid = pcc.CustomerID, @merchantcustomerid = pcc.MerchantCustomerID,
    @carrierid = c.CarrierID, @smsnumber = c.SMSNumber 
    FROM PreCampaignCustomer pcc
    INNER JOIN Customer c ON c.ID = pcc.CustomerID
    WHERE pcc.Processed = 0 AND pcc.CampaignID = @campaignid
    AND pcc.ErrorCount < 5
    ORDER BY pcc.ErrorCount

    --set the couponcode    
    IF @couponlength = -1 
    BEGIN
        SET @couponcode = 'NOCOUPON'
    END
    ELSE
    BEGIN
        EXEC [GenerateCouponCode]
        @length = 9,
        @couponcode = @couponcode OUTPUT
    END

    BEGIN TRY
        --use try/catch just in case the coupon code is repeated or any other error

        --Set the coupon text
        DECLARE @coupontext NVARCHAR(200),
                @smsrequestxml XML


        IF @coupontypecode = 1 --NONE
        BEGIN 
            SET @coupontext = @merchantname + ': ' + @smsmessage + ', Use Code: ' + dbo.FormatCouponCode(@couponcode, @couponcodegrouping) + '. Reply STOP to quit'
        END
        ELSE
        BEGIN
            SET @coupontext = @merchantname + ': ' + @smsmessage + '. Reply STOP to quit'
        END

        EXEC GetSMSRequest @config = @smsconfig, 
                    @smsType = 1, --Submit
                    @address = @smsnumber,
                    @carrierID = @carrierid,
                    @message = @coupontext,
                    @xml = @smsrequestxml OUTPUT

        BEGIN TRAN
            --create the CampaignCustomer record
            INSERT INTO CampaignCustomer
            (CampaignID, CustomerID, CouponCode, Active)
            VALUES
            (@campaignid, @customerid, @couponcode, 1)

            --Add the record to the queue
            INSERT INTO SMSQueue
            (CarrierID, [Address], [Message], TimeToSend, RequestXML, QueueID, HTTPStatusCode, Retries)
            VALUES
            (@carrierid, @smsnumber, @coupontext, @timetosend, @smsrequestxml, @queueid, 0, 0)

            --Create Outgoing SMS Log
            INSERT INTO SMSOutgoingLog
            (MerchantID, MerchantGroupID, MessageTypeCode, CreatedDate, Active)
            VALUES 
            (@merchantid, @merchantgroupid, @messagetypecode, GETDATE(), 1)

            --Update the LastSentSMSTime of the MerchantCustomer
            UPDATE MerchantCustomer
            SET LastSentSMS = GETDATE(),
            ModifiedDate = GETDATE()
            WHERE ID = @merchantcustomerid

            UPDATE PreCampaignCustomer
            SET Processed = 1,
            ModifiedDate = GETDATE()
            WHERE CustomerID = @customerid 
            AND CampaignID = @campaignid
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN

        -- Set the error
        UPDATE PreCampaignCustomer
        SET ErrorCount = ErrorCount + 1,
        ModifiedDate = GETDATE(),
        ErrorMessage = ERROR_MESSAGE(),
        ErrorNumber = ERROR_NUMBER()
        WHERE CustomerID = @customerid 
        AND CampaignID = @campaignid
    END CATCH

    SELECT @count = COUNT(*)
    FROM PreCampaignCustomer
    WHERE Processed = 0 AND CampaignID = @campaignid 
    AND ErrorCount < 5
END

Answer 1

no, the INSERT is a single command. Transactions control how multiple commands are combined together into single units of work, and not how rows are combined within a single command. You can't have some rows INSERT and the ones that fail (some constraint issue) and just be ignored. if any rows fail, then the entire INSERT fails.

why not try modifying the SELECT to exclude rows that will fail?

something like:

INSERT INTO YourTable
        (col1, col2, col3)
    SELECT
        colA, colB, ColC
        FROM YourOtherTable
        WHERE ColA NOT IN (SELECT col1 FROM YourTable)

Answer 2

It is possible to control behaviour of transansactions using SET XACT_ABORT OFF (ON) -- more here.

Answer 3

Thinking out of the box, if you use SSIS to do this, you can send your failed rows down a differnt path or just throw them out.

Answer 4

You're probably thinking about the IGNORE_DUP_KEY property of a unique index.

See this related SO question and the official MSDN article on IGNORE_DUP_KEY.

You'll have to either use ALTER INDEX to add this property, or (if the unique constraint is on the primary key) drop and re-create it.

Once this is in place, any inserts should only reject invalid rows instead of the entire batch.