Как я могу вставить данные в две таблицы одновременно в SQL Server?

Скажем, моя структура таблицы выглядит примерно так:

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

Поле [id] первой таблицы соответствует полю [table1_id] второго. То, что я хотел бы сделать, это вставить данные в обе таблицы в одну транзакцию. Теперь я уже знаю, как это сделать, выполняя INSERT-SELECT-INSERT, например:

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;

Это все хорошо и прекрасно для небольших случаев, например, где вы только вставляете, может быть, несколько строк. Но мне нужно вставить пару сотен тысяч строк или, возможно, даже миллион строк, все сразу. Данные поступают из другой таблицы, поэтому, если бы я только вставлял ее в одну таблицу, это было бы просто, я просто должен был бы сделать это:

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

Но как бы я это сделал и разделил данные на [table1] и [table2] и все еще обновил [table2] с помощью соответствующего [table1_id], как я это делаю? Возможно ли это?

Ответ 1

Попробуйте следующее:

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

ОБНОВЛЕНИЕ: Re:

Денис - это кажется очень близким к тому, что я хочу сделать, но, возможно, вы могли бы исправить для меня следующий оператор SQL? В основном [данные] в [table1] и [data] в [table2] представляют два разных/отдельных столбца из [external_table]. Вышеуказанное утверждение работает только тогда, когда вы хотите, чтобы столбцы [data] были одинаковыми.

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

Невозможно выводить внешние столбцы в инструкции insert, поэтому я думаю, что вы могли бы сделать что-то вроде этого

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

Ответ 2

Я также боролся с этой проблемой и считаю, что лучший способ - использовать CURSOR.

Я попробовал решение Denis с OUTPUT, но, как он упоминает, невозможно выводить внешние столбцы в инструкции insert, а MERGE не может работать, когда вы вставляете несколько строк по выбору.

Итак, я использовал CURSOR, для каждой строки во внешней таблице я сделал INSERT, а затем использовал @@IDENTITY для другого 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

Ответ 3

Следите за тем, чтобы SQL Server поддерживал оператор INSERT ALL. У Oracle это уже есть, это выглядит так (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

Ответ 4

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;

Ответ 5

Еще один вариант - запустить две вставки отдельно, оставив столбец FK нулевым, а затем запустив обновление, чтобы правильно его очистить.

Если в двух таблицах нет ничего естественного, которые соответствуют одной записи другим (скорее всего), создайте временный столбец GUID и заполните это в своих данных и вставьте в оба поля. Затем вы можете обновить с помощью правильного FK и исключить GUID.

Например:

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

Ответ 6

Create table #temp1
(
 id int identity(1,1),
 name varchar(50),
 profession varchar(50)
)

Create table #temp2
(
 id int identity(1,1),
 name varchar(50),
 profession varchar(50)
)

-----main запрос ------

insert into #temp1(name,profession)

output inserted.name,inserted.profession into #temp2

select 'Shekhar','IT'

Ответ 7

Вы можете написать хранимую процедуру, которая выполняет итерацию по транзакции, которую вы предложили. Итератором будет курсор для таблицы, содержащей исходные данные.