MS SQL Server
  1. MS SQL Server
  3. Combinaison d'instructions INSERT et UPDATE (procédure stockée SQL2005)
Intereting Posts
SUMPRODUCT En SQL Utilisez SMO pour modifier les locations par défaut de la database SQL Server TSQL: Comment searchr une note dans une list de notes? Données de sorting SQL utilisant deux tables différentes Comment get la valeur partielle d'une valeur de nœud XML Mise à jour MS SQL Trigger appel verrou mort? SQL Server 2008 Existe-t-il un moyen plus efficace de faire cette boucle de mise à jour? Essayer d'installer la database adventureworks 2016 Nom de colonne invalide alors que la colonne est là Vérification des parameters nécessaires de la procédure stockée avec PowerShell Échec de la restauration des bases de données du server Team Foundation Importer XML dans SQL Server, divisé en plusieurs lignes Message d'erreur: Fin prématurée des en-têtes de script Comment déplacer datatables de la database mssql vers Access? Comment éviter deux champs avec le même nom dans deux tables différentes pour prendre la même valeur en sql?

Combinaison d'instructions INSERT et UPDATE (procédure stockée SQL2005)

J'ai besoin d'extraire des loggings d'une table, de copyr datatables dans une deuxième table et de mettre à jour les loggings dans la première table pour indiquer qu'ils ont été copiés avec succès.

Mon code SP actuel est le suivant: 

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

Maintenant, une fois que le ci-dessus est effectuée, j'ai besoin d'insert ces données dans une deuxième table appelée TBL_RECIPIENTS. Supposons que les colonnes sont simplement nommées col_1, col_2, col_3 …. col_5 dans TBL_ADDRESSBOOKADDRESSES et que c'est la même chose dans TBL_RECIPIENTS.

Une fois cette action effectuée, j'ai besoin de mettre à jour TBL_CAMPAIGNS.campaign_Status = 2 Idéalement, cela ne devrait être que pour les loggings qui ont été réellement mis à jour (dans le cas où le script serait arrêté en raison d'un crash du server, etc.)

S'il vous plaît laissez-moi savoir si vous avez besoin de quelque chose de clarification.

Merci beaucoup!

J'ai pris le conseil aimablement donné ci-dessous et viens avec le code de travail ci-dessous. J'ai lu un tutoriel qui suggérait d'append try / catch pour assurer le rollback si des erreurs surviennent. Mon code ci-dessous est-il adéquat à cet égard?

Toute suggestion serait accueilli avec reconnaissance.

Merci. 

 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 copys 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 copyd 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

J'ai fait quelques suppositions sur votre structure, les jointures peuvent ne pas être correctes 

 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

Remarque: Je n'ai pas utilisé select * qui ne devrait jamais être utilisé dans le code de production. J'ai aussi utilisé des alias pour rendre le code plus facile à lire.

Avez-vous envisagé de tout mettre dans une transaction?

Exemple: 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

Si la mise à jour ou la suppression donne une erreur, la transaction sera annulée. Si le système se bloque avant la validation, le server SQL effectue un return en arrière, car il existe une transaction non validée

Une façon consiste à utiliser la clause OUTPUT ; Dans ce cas, vous pouvez sélectionner tous les ID de carnet d'adresses que vous avez insérés et les utiliser pour mettre à jour les campagnes. Pourtant, vous auriez vraiment besoin d'utiliser des transactions si vous voulez get ceci fiable, avec un verrou qui empêchera les mises à jour et les insertions . 

 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

Si l'ID de la campagne faisait partie de la table TBL_RECIPIENTS , vous seriez certain à 100% que vous avez obtenu le bon ID de campagne via la clause OUTPUT et que les transactions de locking ne seraient pas vraiment nécessaires. Pour autant que je sache, SQL Server ne vous permet pas de faire reference aux colonnes des tables jointes dans la clause OUTPUT (contrairement à DELETE ... OUTPUT et UPDATE ... OUTPUT ), donc l'ID de campagne doit faire partie de l' INSERT ' s Clause SELECT .

Parce que l'identifiant de la campagne ne fait pas partie de la sortie, cette solution est à peu près une version compliquée de la réponse de HLGEM, mais elle pourrait vous être utile à l'avenir.

Vous devez stocker l'ID d'adresse mis à jour dans une variable de table remplie à l'aide de la clause OUTPUT de 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