Comment puis-je INSÉRER des données dans deux tables en même temps dans SQL Server?

Disons que mon tableau de structure ressemble à quelque chose comme ceci:

CREATE TABLE [dbo].[table1] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [data] [varchar](255) NOT NULL,
    CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
)

CREATE TABLE [dbo].[table2] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [table1_id] [int] NOT NULL,
    [data] [varchar](255) NOT NULL,
    CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
)

La [id] champ de la première table correspond à la [table1_id] champ de la seconde. Ce que je voudrais faire est d'insérer des données dans deux tables en une seule transaction. Maintenant, je sais déjà comment faire cela en faisant INSÉRER-SÉLECTIONNEZ-INSÉRER, comme ceci:

BEGIN TRANSACTION;
DECLARE @id [int];
INSERT INTO [table1] ([data]) VALUES ('row 1');
SELECT @id = SCOPE_IDENTITY();
INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, 'more of row 1');
COMMIT TRANSACTION;

C'est tout bon et très bien pour de petites affaires comme celle où vous êtes seule l'insertion, peut-être une poignée de lignes. Mais ce dont j'ai besoin pour faire est d'insérer un couple de centaines de milliers de lignes, ou peut-être même un million de lignes, tout à la fois. Les données proviennent d'une autre table, donc si je n'avais que de l'insérer dans une table unique, il serait facile, que je venais de faire ceci:

INSERT INTO [table] ([data])
SELECT [data] FROM [external_table];

Mais comment pourrais-je faire cela et de diviser les données en [table1] et [table2], et de toujours mettre à jour [table2] avec le [table1_id] que je suis en train de faire? Est-il même possible?

Belle question beaucoup de débutants de SQL demandent souvent.

InformationsquelleAutor soapergem | 2010-09-14

30

Essayez ceci:
```
insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]
```
Mise à JOUR: Re:

Denis - cela semble très proche de ce que je veux faire, mais peut-être que vous pourriez fixer l'instruction SQL suivante pour moi? Fondamentalement, les [données] dans [table1] et de la [des données] dans [table2] représentent deux différentes colonnes à partir de [external_table]. La déclaration que tu as posté ci-dessus ne fonctionne que lorsque vous voulez [data] les colonnes d'être le même.
```
INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table] 
```
Il est impossible à la sortie des colonnes externes dans un insert déclaration, donc je pense que vous pourriez faire quelque chose comme ce
```
merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;
```
- Denis, je ne l'ai utilisé en SORTIE pour écrire dans une variable de table. Pouvez-vous l'utiliser pour insérer directement dans une table en direct?
- vous betcha!
- En passant, il n'y a pas besoin de l'embrasser dans begin tran... commit tran états, comme c'est manifestement va être exécuté en une seule transaction.
- Denis - cela semble très proche de ce que je veux faire, mais peut-être que vous pourriez fixer l'instruction SQL suivante pour moi? Fondamentalement, les [données] dans [table1] et de la [des données] dans [table2] représentent deux différentes colonnes à partir de [external_table]. La déclaration que tu as posté ci-dessus ne fonctionne que lorsque vous voulez [data] les colonnes d'être le même. INSERT INTO [table1] ([data]) OUTPUT [inserted].[id], [external_table].[col2] INTO [table2] SELECT [col1] FROM [external_table]
- J'ai d'abord eu excité par cela, mais ont depuis ce message d'erreur: "La table cible de la SORTIE DANS la clause ne peut pas avoir de permis de vérifier les contraintes" -- aucune chance que ça se passe! Oh, eh bien, à l'aide d'une mise en scène de la table temporaire ou une variable de table pour stocker le résultat. Bon à savoir en tout cas 🙂
- Ahahaha! :))) Oui, il y a beaucoup de choses qui n'ont pas besoin d'être dans la table de sortie (fk, les contraintes, les déclencheurs), donc en gros c'est inutile, sauf pour la mise en scène des tables.
- Merci beaucoup Denis. C'est ce que je cherchais, alors j'ai accepté votre réponse. Cependant, juste par souci d'exhaustivité, et si vous vous souciez de vous... est-il un moyen de le faire dans SQL Server 2005, où la FUSION n'était pas disponible?
- Seulement si vous avez créé ce col2 colonne dans la table1. Alors vous seriez en mesure de produire l' inserted.col2 dans le table2.
InformationsquelleAutor Denis Valeev

J'ai aussi été aux prises avec ce problème, et de trouver que le meilleur moyen est d'utiliser un CURSEUR.

J'ai essayé Denis solution de SORTIE, mais comme il mentiond, il est impossible à la sortie des colonnes externes dans une instruction insert, et que la FUSION ne peut pas travailler quand insérer plusieurs lignes en sélectionner.

Donc, j'ai utilisé un CURSEUR, pour chaque ligne de la table externe, j'ai fait INSÉRER, puis utilisez le @@IDENTITY pour INSÉRER un autre.

DECLARE @OuterID int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT  ID FROM   [external_Table]

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @OuterID

WHILE @@FETCH_STATUS = 0
BEGIN 
INSERT INTO [Table]   (data)
    SELECT data
    FROM     [external_Table] where ID = @OuterID 

    INSERT INTO [second_table] (FK,OuterID)
    VALUES(@OuterID,@@identity)

    FETCH NEXT FROM MY_CURSOR INTO @OuterID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

InformationsquelleAutor IFink

Garder un oeil sur pour SQL Server pour prendre en charge le 'INSÉRER' Instruction. Oracle a déjà, il ressemble à ceci (SQL livre de recettes):

insert all
  when loc in ('NEW YORK', 'BOSTON') THEN
   into dept_east(deptno, dname, loc) values(deptno, dname, loc)
  when loc in ('CHICAGO') THEN
   into dept_mid(deptno, dname, loc) values(deptno, dname, loc)
  else
   into dept_west(deptno, dname, loc) values(deptno, dname, loc)
select deptno, dname, loc
  from dept

InformationsquelleAutor Brian

BEGIN TRANSACTION;

DECLARE @tblMapping table(sourceid int, destid int)

INSERT INTO [table1] ([data]) 
OUTPUT source.id, new.id
Select [data] from [external_table] source;

INSERT INTO [table2] ([table1_id], [data])
Select map.destid, source.[more data] 
from [external_table] source
    inner join @tblMapping map on source.id=map.sourceid;

COMMIT TRANSACTION;

Compte tenu de Denis de réponse à mon commentaire, sa solution est beaucoup plus propre que la mienne.
Vous ne pouvez pas utiliser source.id, new.id dans le output clause. Vous êtes uniquement autorisé à utiliser inserted.* là pour insert. Pour delete, update et merge il est possible d'inclure une colonne de la table spécifiée.

InformationsquelleAutor Bill

0

Une autre option consiste à exécuter les deux inserts séparément, laissant le FK colonne nulle, alors l'exécution d'une mise à jour de poulate correctement.

Si il n'y a rien de naturel stocké dans les deux tableaux qui correspondent à partir d'un enregistrement à l'autre (probablement) puis créer un temporaire GUID de la colonne et le remplir de vos données et de les insérer des deux domaines. Ensuite, vous pouvez mettre à jour avec le bon FK et null le Guid.

E. g.:
```
CREATE TABLE [dbo].[table1] ( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [data] [varchar](255) NOT NULL, 
    CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC),
    JoinGuid UniqueIdentifier NULL
) 

CREATE TABLE [dbo].[table2] ( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [table1_id] [int] NULL, 
    [data] [varchar](255) NOT NULL, 
    CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC),
    JoinGuid UniqueIdentifier NULL
) 


INSERT INTO Table1....

INSERT INTO Table2....

UPDATE b
SET table1_id = a.id
FROM Table1 a
JOIN Table2 b on a.JoinGuid = b.JoinGuid
WHERE b.table1_id IS NULL

UPDATE Table1 SET JoinGuid = NULL
UPDATE Table2 SET JoinGuid = NULL
```
- Comment définir même JoinGuid pour les deux tableaux? C'est simple si vous avez un enregistrement dans Table1 et un enregistrement dans Table2, mais je ne peux pas imaginer comment l'implémenter dans le cas d'un grand nombre de lignes.
- À un certain moment dans votre code, vous devriez être en mesure de relier les données, ajouter le guid à ce point.
InformationsquelleAutor cjk
-1

Vous pourriez écrire une procédure stockée qui effectue une itération sur l'opération que vous avez proposé. L'itérateur serait le curseur de la table qui contient la source de données.
- Ne jamais effectuer une itération quand il y a un jeu à base de solution.
- compte tenu de mon erreur, laquelle des solutions proposées recommanderiez-vous?
- Certainement la sortie de la clause à une table temp.
InformationsquelleAutor mlschechter

Vous devez vous connecter pour publier un commentaire.