Comment puis-je INSÉRER des données dans deux tables simultanément dans SQL Server?

Question

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)
)

L' [id] de champ de la première table correspond à l' [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 encore de mise à jour de [table2] avec le cas échéant [table1_id] que je suis en train de faire? Est-il même possible?

Answer 1

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 en insert déclaration, donc je pense que vous pourriez faire quelque chose comme ceci

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]
;

Answer 2

Gardez un œil sur SQL Server pour qu'il prenne en charge l'instruction 'INSERT ALL'. Oracle l'a déjà, ça ressemble à ça ( 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
 

Answer 3

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;

Answer 4

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 contenant les données source.

Answer 5

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 générer correctement.

S'il n'y a rien de naturel stocké dans les deux tables qui correspond d'un enregistrement à un autre (probablement), créez une colonne GUID temporaire, 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