Combining INSERT and UPDATE statement (SQL2005 Stored Procedure)

Question

Hi,

I need to extract records from a table, copy the data to a second table and then update the records in the first table to indicate that they have been copied across successfully.

My current SP code is this:

SELECT TBL_ADDRESSBOOKADDRESSES.* FROM TBL_ADDRESSBOOKADDRESSES 
INNER JOIN TBL_CAMPAIGNS
ON TBL_ADDRESSBOOKADDRESSES.adds_ABMId = TBL_CAMPAIGNS.campaign_AddressBook
WHERE TBL_CAMPAIGNS.campaign_Status = 1

Now once the above is performed i need to insert this data into a second table called TBL_RECIPIENTS. Assume that the columns are simply named col_1, col_2, col_3 .... col_5 in TBL_ADDRESSBOOKADDRESSES and that this is the same in TBL_RECIPIENTS.

Once this action is performed i need to update TBL_CAMPAIGNS.campaign_Status = 2 Ideally this should only be for those records that have actually been updated(in case script gets stopped mid way through due to server crash etc)

Please let me know if you need anything clarifying.

Many Thanks!

---

Ive taken the advise kindly given below and come up with the working code below. Ive read tutorial which suggested adding try/catch to ensure rollback if any errors occurr. Is my code below adequate in this respect??

Any suggest would be gratefully received.

Thanks.

CREATE PROCEDURE web.SERVER_create_email_recipients
AS
BEGIN TRY
  --sets (n) campaigns ready for transfer of emails to mailing list
      UPDATE TOP(1) TBL_CAMPAIGNS
  SET TBL_CAMPAIGNS.campaign_Status = 1
  WHERE TBL_CAMPAIGNS.campaign_Status = 0

  --finds above marked campaigns, retreives addresses then copies them to     TBL_CAMPAIGNRECIPIENTS ready for auto mailout
  INSERT TBL_CAMPAIGNRECIPIENTS (recip_CampaignId, recip_Email, recip_Forename, recip_Surname, recip_adds_Key)
  SELECT C.Campaign_AddressBook, ABA.adds_Email, ABA.adds_RecipientForename,     ABA.adds_RecipientSurname, ABA.adds_Key
  FROM TBL_ADDRESSBOOKADDRESSES ABA
  JOIN TBL_CAMPAIGNS C ON ABA.adds_ABMId = C.campaign_AddressBook
  WHERE C.campaign_Status = 1

  --checks that above emails have been copied across and then sets the campaigns status accordingly
  UPDATE C
  SET C.campaign_Status = 2
  From TBL_CAMPAIGNS C
  JOIN TBL_ADDRESSBOOKADDRESSES  aba
  ON aba.adds_ABMId = C.campaign_AddressBook
  JOIN TBL_CAMPAIGNRECIPIENTS r on aba.adds_Key = r.recip_adds_Key
  WHERE C.campaign_Status = 1

END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  --throws out error to logs?
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Answer 1

Have you considered to put it all inside a transaction?

Example: DECLARE @ErrorCode INT

BEGIN TRAN
  UPDATE Authors
  SET Phone = '911'
  WHERE au_id = 2

  SELECT @ErrorCode = @@ERROR
  IF (@intErrorCode <> 0) GOTO ErrExit

  DELETE Authors WHERE Phone = '911' au_id <> 2 

  SELECT @ErrorCode = @@ERROR
  IF @ErrorCode <> 0) GOTO ErrExit
COMMIT TRAN

ErrExit;
IF (@intErrorCode <> 0) 

ROLLBACK TRAN

If the update or the delete gives an error the transaction will be rolled back. If the system crashes before the commit the sql server does a rollback, because there is an uncommited transaction

Answer 2

I've made some guesses about your structure, the joins may not be correct

INSERT TBL_RECIPIENTS (Col1, Col2, COl3)
SELECT ABA.Col1, ABA.Col2,ABA.Col3
FROM TBL_ADDRESSBOOKADDRESSES ABA
INNER JOIN TBL_CAMPAIGNS C ON ABA.adds_ABMId = C.campaign_AddressBook
WHERE TBL_CAMPAIGNS.campaign_Status = 1

UPDATE C
SET C.campaign_Status = 2
From TBL_CAMPAIGNS C
JOIN TBL_ADDRESSBOOKADDRESSES  aba
 on aba.adds_ABMId = C.campaign_AddressBook
JOIN TBL_RECIPIENTS r on aba.id = r.sameid
WHERE TBL_CAMPAIGNS.campaign_Status = 1

Note I did not use select * which should never be used in production code. Also I used aliases to make the code easier to read.

Answer 3

One way is to use the OUTPUT clause; in this case, you could select all address book ID's you've inserted, and use that to update the campaigns. Still, you'd really need to use transactions if you want to get this reliable, with a lock that will prevent both updates and inserts.

DECLARE @addressBookIds TABLE(AddressBookId INT NOT NULL)

INSERT INTO TBL_RECIPIENTS
OUTPUT INSERTED.adds_ABMId INTO @addressBookIds
SELECT TBL_ADDRESSBOOKADDRESSES.* 
FROM   TBL_ADDRESSBOOKADDRESSES
INNER JOIN TBL_CAMPAIGNS
  ON   TBL_ADDRESSBOOKADDRESSES.adds_ABMId = TBL_CAMPAIGNS.campaign_AddressBook
WHERE  TBL_CAMPAIGNS.campaign_Status = 1

UPDATE TBL_CAMPAIGNS
SET [campaign_Status] = 2
FROM TBL_CAMPAIGNS
INNER JOIN @addressBookIds AS T
ON TBL_CAMPAIGNS.campaign_AddressBook = T.AddressBookId
AND TBL_CAMPAIGNS.campaign_Status = 1

If the campaign ID was part of TBL_RECIPIENTS table, you'd be 100% certain you've got the right campaign ID's through the OUTPUT clause and locking transactions would not really be required. As far as I can tell, SQL Server does not allow you to refer to columns from joined tables in the OUTPUT clause (unlike DELETE ... OUTPUT and UPDATE ... OUTPUT), so the campaign ID must be part of the INSERT's SELECT clause.

Because the campaign ID is not part of the output, this solution is pretty much a complicated version of HLGEM's answer, but it could be useful to you in the future.

Answer 4

You should store the updated address id into a table variable populated using the OUTPUT clause of INSERT:

create table TBL_ADDRESSBOOKADDRESSES (
    adds_ABMID int identity(1,1) not null,
    col_1 varchar(100),
    col_2 varchar(100));
go

create table TBL_RECIPIENTS (
    adds_ABMID int not null, 
    col_1 varchar(100),
    col_2 varchar(100));
go

create table TBL_CAMPAIGNS (
    campaign_AddressBook int,
    campaign_Status int);
go

insert into TBL_ADDRESSBOOKADDRESSES (col_1, col_2)
    values ('spam', 'is evil');
insert into TBL_ADDRESSBOOKADDRESSES (col_1, col_2)
    values ('all mass mail', 'is spam');


insert into TBL_CAMPAIGNS (campaign_AddressBook, campaign_Status)
    values (1,1);
insert into TBL_CAMPAIGNS (campaign_AddressBook, campaign_Status)
    values (2,1);
go  

set nocount on;
declare @newRecipients table (adds_ABMID int);
begin transaction
insert into TBL_RECIPIENTS (adds_ABMID, col_1, col_2)
    output inserted.adds_ABMID into @newRecipients
    SELECT a.adds_ABMID
     , a.col_1
     , a.col_2
    FROM TBL_ADDRESSBOOKADDRESSES a 
    INNER JOIN TBL_CAMPAIGNS ON a.adds_ABMId = TBL_CAMPAIGNS.campaign_AddressBook
    WHERE TBL_CAMPAIGNS.campaign_Status = 1
update TBL_CAMPAIGNS 
    set campaign_Status = 2
    from TBL_CAMPAIGNS as c
    join @newRecipients as new on c.campaign_AddressBook = new.adds_ABMID;
commit; 
go

select * from TBL_RECIPIENTS;
select * from TBL_CAMPAIGNS;
go