Comment puis-je insérer des données dans deux tables simultanément dans SQL Server?

Disons que ma structure de table ressemble à 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)
)

Le Champ [id] de la première table correspond au champ [table1_id] de la seconde. Ce que je voudrais faire est d'insérer des données dans les deux tables dans une seule transaction. Maintenant, je sais déjà comment faire cela en faisant INSERT-SELECT-INSERT, 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 bien pour les petits cas comme celui où vous n'insérez que peut-être une poignée de lignes. Mais ce que je dois faire est d'insérer quelques centaines mille lignes, ou peut-être même un million de lignes, tout à la fois. Les données proviennent d'une autre table, donc si Je ne l'insérais que dans une seule table, ce serait facile, je devrais juste faire ceci: 

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

Mais comment ferais-je cela et diviser les données en [table1] et [table2], et encore mettre à jour [table2] avec le [table1_id] approprié comme je le fais? Est-il même possible?

50
demandé sur SoaperGEM 2010-09-15 00:34:06

6 réponses

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 pourriez-vous corriger L'instruction SQL suivante pour moi? Fondamentalement, les [data] dans [table1] et les [data] dans [table2] représentent deux colonnes différentes/distinctes de [external_table]. L'instruction que vous avez publiée ci-dessus ne fonctionne que lorsque vous voulez que les colonnes [data] soient les mêmes. 

INSERT INTO [table1] ([data]) 
OUTPUT [inserted].[id], [external_table].[col2] 
INTO [table2] SELECT [col1] 
FROM [external_table]

Il est impossible de générer des colonnes externes dans une instruction insert , donc, je pense que vous pourriez faire quelque chose comme ça 

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]
;
29
répondu Denis Valeev 2010-09-15 07:03:46

J'étais aussi aux prises avec ce problème, et je trouve que le meilleur moyen est d'utiliser un curseur .

J'ai essayé la solution Denis avec OUTPUT, mais comme il l'a mentionné, il est impossible de sortir des colonnes externes dans une instruction insert, et la fusion ne peut pas fonctionner lorsque vous insérez plusieurs lignes par select.

Donc, j'ai utilisé un curseur, pour chaque ligne de la table externe, j'ai fait un INSERT, puis utilisez l'identité @@pour un autre INSERT.

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
4
répondu IFink 2017-10-27 14:05:21

Gardez un œil sur SQL Server pour prendre en charge L'instruction 'INSERT ALL'. Oracle l'a déjà, il ressemble à ceci (SQL Cookbook):

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
1
répondu Brian 2010-09-14 21:00:12 
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;
0
répondu Bill 2010-09-14 20:54:49

Une autre option consiste à exécuter les deux insertions séparément, en laissant la colonne FK null, puis en exécutant une mise à jour pour la poulater correctement.

S'il n'y a rien de naturel stocké dans les deux tables qui correspondent d'un enregistrement à un autre (probable), créez une colonne GUID temporaire et remplissez-la dans vos données et insérez-la dans les deux champs. Ensuite, vous pouvez mettre à jour avec le FK approprié et annuler les GUID.

Par exemple: 

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
0
répondu cjk 2010-09-15 07:10:52

Vous pouvez écrire une procédure stockée qui itère sur la transaction que vous avez proposée. L'itérateur serait le curseur de la table qui contient les données source.

-1
répondu mlschechter 2010-09-14 20:44:41