SQL Server : Est-il possible d'insérer dans deux tables en même temps ?

Question

Ma base de données contient trois tables appelées Object_Table , Data_Table y Link_Table . La table de liaison ne contient que deux colonnes, l'identité d'un enregistrement d'objet et l'identité d'un enregistrement de données.

Je veux copier les données de DATA_TABLE où il est lié à une identité d'objet donnée et insérer les enregistrements correspondants dans Data_Table y Link_Table pour une identité d'objet donnée différente.

I peut faire cela en sélectionnant dans une variable de table et en bouclant en faisant deux insertions pour chaque itération.

Est-ce la meilleure façon de procéder ?

Modifier : Je veux éviter une boucle pour deux raisons, la première est que je suis paresseux et qu'une boucle/table d'attente nécessite plus de code, plus de code signifie plus d'endroits pour faire une erreur et la deuxième raison est un souci de performance.

Je peux copier toutes les données en une seule insertion, mais comment faire pour que la table de liens soit liée aux nouveaux enregistrements de données où chaque enregistrement a un nouvel identifiant ?

Answer 1

Dans un déclaration : Non.

Dans un transaction : Oui

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

La bonne nouvelle est que le code ci-dessus est également garanti pour être atomique et peuvent être envoyées au serveur à partir d'une application client avec une chaîne sql dans un appel de fonction unique, comme s'il s'agissait d'une seule déclaration. Vous pouvez également appliquer un déclencheur à une table pour obtenir l'effet d'une seule insertion. Cependant, il s'agit en fin de compte de deux instructions et vous ne souhaitez probablement pas exécuter le déclencheur pour une seule insertion. chaque insérer.

Answer 2

Vous avez toujours besoin de deux INSERT mais il semble que vous souhaitiez obtenir les déclarations de IDENTITY de la première insertion et de l'utiliser dans la seconde, auquel cas, vous pourriez vouloir regarder dans OUTPUT o OUTPUT INTO : http://msdn.microsoft.com/en-us/library/ms177564.aspx

Answer 3

Ce qui suit met en place la situation que j'avais, en utilisant des variables de tableau.

DECLARE @Object_Table TABLE
(
    Id INT NOT NULL PRIMARY KEY
)

DECLARE @Link_Table TABLE
(
    ObjectId INT NOT NULL,
    DataId INT NOT NULL
)

DECLARE @Data_Table TABLE
(
    Id INT NOT NULL Identity(1,1),
    Data VARCHAR(50) NOT NULL
)

-- create two objects '1' and '2'
INSERT INTO @Object_Table (Id) VALUES (1)
INSERT INTO @Object_Table (Id) VALUES (2)

-- create some data
INSERT INTO @Data_Table (Data) VALUES ('Data One')
INSERT INTO @Data_Table (Data) VALUES ('Data Two')

-- link all data to first object
INSERT INTO @Link_Table (ObjectId, DataId)
SELECT Objects.Id, Data.Id
FROM @Object_Table AS Objects, @Data_Table AS Data
WHERE Objects.Id = 1

Grâce à un autre respuesta qui m'a orienté vers la clause OUTPUT, je peux démontrer une solution :

-- now I want to copy the data from from object 1 to object 2 without looping
INSERT INTO @Data_Table (Data)
OUTPUT 2, INSERTED.Id INTO @Link_Table (ObjectId, DataId)
SELECT Data.Data
FROM @Data_Table AS Data INNER JOIN @Link_Table AS Link ON Data.Id = Link.DataId
                INNER JOIN @Object_Table AS Objects ON Link.ObjectId = Objects.Id 
WHERE Objects.Id = 1

Il s'avère toutefois que ce n'est pas aussi simple dans la réalité, en raison de l'erreur suivante

la clause OUTPUT INTO ne peut pas se situer de de part et d'autre d'une relation (clé primaire, clé étrangère)

Je peux toujours OUTPUT INTO une table temporaire et ensuite terminer avec une insertion normale. Je peux donc éviter ma boucle mais je ne peux pas éviter la table temporaire.

Answer 4

Si vous voulez que les actions soient plus ou moins atomiques, je ferais en sorte de les envelopper dans une transaction. De cette façon, vous pouvez être sûr que les deux actions se sont produites ou qu'elles ne se sont pas produites comme il se doit.

Answer 5

Vous pouvez créer une vue en sélectionnant les noms de colonnes requis par votre instruction d'insertion, ajouter un déclencheur INSTEAD OF INSERT et effectuer une insertion dans cette vue.