我需要从一个表中提取记录，将数据复制到第二个表中，然后更新第一个表中的记录以指示它们已成功复制。
我当前的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
理想情况下，这仅应用于实际上已更新的那些记录（以防脚本由于服务器崩溃等原因而中途停止）
如果您需要任何澄清，请告诉我。
非常感谢！
我接受了下面的建议，并提出了下面的工作代码。香港专业教育学院阅读教程，建议添加尝试/捕获，以确保发生任何错误时回滚。我的以下代码在这方面是否足够？
任何建议将不胜感激。
谢谢。
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
最佳答案
我对您的结构做了一些猜测，联接可能不正确
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
请注意，我没有使用select *，这绝对不能在生产代码中使用。我还使用别名使代码更易于阅读。
关于sql - 合并INSERT和UPDATE语句(SQL2005存储过程)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1255906/