Comment insérer plusieurs enregistrements et obtenir la valeur d'identité ?

Question

J'insère plusieurs enregistrements dans une table A à partir d'une autre table B. Existe-t-il un moyen d'obtenir la valeur d'identité de l'enregistrement de la table A et de mettre à jour l'enregistrement de la table B sans avoir recours à un curseur ?

Create Table A
(id int identity,
Fname nvarchar(50),
Lname nvarchar(50))

Create Table B
(Fname nvarchar(50),
Lname nvarchar(50),
NewId int)

Insert into A(fname, lname)
SELECT fname, lname
FROM B

J'utilise MS SQL Server 2005.

Answer 1

Utilisez la clause de sortie de 2005 :

DECLARE @output TABLE (id int)

Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B

select * from @output

maintenant votre variable table a les valeurs d'identité de toutes les lignes que vous insérez.

Answer 2

En lisant attentivement votre question, vous souhaitez simplement mettre à jour la table B sur la base des nouvelles valeurs d'identité de la table A.

Une fois l'insertion terminée, il suffit de lancer une mise à jour...

UPDATE B
SET NewID = A.ID
FROM B INNER JOIN A
     ON (B.FName = A.Fname AND B.LName = A.LName)

Cela suppose que la combinaison FName / LName puisse être utilisée pour faire correspondre les enregistrements entre les tables. Si ce n'est pas le cas, vous devrez peut-être ajouter des champs supplémentaires pour garantir que les enregistrements correspondent correctement.

Si vous ne disposez pas d'une clé alternative vous permettant de faire correspondre les enregistrements, cela n'a aucun sens, puisque les enregistrements de la table B ne peuvent pas être distingués les uns des autres.

Answer 3

La réponse d'Andy Irving est la meilleure.

Les déclencheurs sont maladroits et ne fonctionnent pas bien pour les opérations arbitraires sur votre table cible, en particulier si votre cible est temporaire ou simplement intermédiaire.

La réponse de Darren est erronée. Si vous insérez un ensemble de lignes, leur ordre dans la table cible n'est pas nécessairement le même que l'ordre de votre ensemble.

La méthode de Dmitry est mauvaise car elle nécessite une boucle autour de l'insertion d'une seule ligne à la fois, ce qui est lent du point de vue des performances, utilisez toujours des ensembles lorsque vous le pouvez.

La méthode de Cory est mauvaise et il a expliqué pourquoi, "tant qu'il n'y a pas de conflit". Cela va se transformer en un appel du samedi soir "Hey Bob, l'application est en train de planter à cause d'une violation de la clé primaire".

Il n'y a plus de raison de faire des suggestions à ce sujet, Andy a tout compris.

Answer 4

Si vous souhaitez toujours obtenir ce comportement, vous pouvez mettre en place un déclencheur AFTER INSERT sur la table A qui mettra à jour la table B.

Answer 5

MBelly a raison - Mais alors le trigger essaiera toujours de mettre à jour la table B même si ce n'est pas nécessaire (parce que vous insérez aussi de la table C ?).

Darren a également raison, il n'est pas possible de récupérer plusieurs identités dans un ensemble de résultats. Vous pouvez utiliser un curseur et prendre l'identité pour chaque ligne que vous insérez, ou utiliser l'approche de Darren qui consiste à stocker l'identité avant et après. Tant que vous connaissez l'incrément de l'identité, cela devrait fonctionner, tant que vous vous assurez que la table est verrouillée pour les trois événements.

Si c'était moi, et si le temps n'était pas compté, j'opterais pour un curseur.