sql - 结合 INSERT 和 UPDATE 语句(SQL2005 存储过程)

Question

我需要从一个表中提取记录，将数据复制到第二个表，然后更新第一个表中的记录以表明它们已成功复制。

我目前的 SP 代码是这样的:

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

现在，一旦执行了上述操作，我需要将此数据插入到名为 TBL_RECIPIENTS 的第二个表中。假设这些列在 TBL_ADDRESSBOOKADDRESSES 中简单命名为 col_1、col_2、col_3 .... col_5，并且这与 TBL_RECIPIENTS 中的相同。

执行此操作后，我需要更新 TBL_CAMPAIGNS.campaign_Status = 2理想情况下，这应该只针对那些实际已更新的记录(以防脚本因服务器崩溃等而中途停止)

如果您需要任何说明，请告诉我。

非常感谢!

---

我采纳了下面的建议，并提出了下面的工作代码。我阅读了建议添加 try/catch 以确保在发生任何错误时回滚的教程。我的代码在这方面是否足够？？

任何建议都将不胜感激。

谢谢。

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

您是否考虑过将其全部放入事务中？

例子:声明@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

如果更新或删除出错，事务将被回滚。如果系统在提交之前崩溃，则 sql server 会回滚，因为存在未提交的事务