Databases
StackOverflow2013
Postsdbo
POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
    primarykey
    data
    text
    <p>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.</p> <p>My current SP code is this:</p> <pre><code>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 </code></pre> <p>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.</p> <p>Once this action is performed i need to update <strong>TBL_CAMPAIGNS.campaign_Status = 2</strong> 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)</p> <p>Please let me know if you need anything clarifying.</p> <p>Many Thanks!</p> <hr> <p>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??</p> <p>Any suggest would be gratefully received. </p> <p>Thanks.</p> <pre><code>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 &gt; 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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMunklefish
    1. USMunklefish
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. USMunklefish
      1. VTFavorite
    2. VO
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. USCheeseConQueso
      1. VTFavorite
    3. VO
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhich database system are you using? Some DBMSes support non-standard ways of doing this that are nice.
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. USHarold L
    2. COSQL 2005 stored procedure.
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. USMunklefish
    3. COHow does tbl_campaigns relate to tbl_recipients? Do I need to join through Tbl_AddressBookAddresses?
      singulars
      1. POCombining INSERT and UPDATE statement (SQL2005 Stored Procedure)
      1. USHLGEM
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload