Зависимые операторы вставки

У меня есть таблица с данными о клиенте, Customer(имя, адрес), с такими строками, как "John Doe", "Some Street 123". Для каждой строки в таблице я хочу вставить одну строку в таблицу Person(id, name), а также одну строку в таблице Address(id, person_id, address).

Я могу сделать это, запустив два оператора вставки для каждой строки в Customer:

insert into Person(name) values (@name);
insert into Address(person_id, address) values (scope_identity(), @address);

Но это неэффективно. Я хочу сделать вставки в пакете, вроде этого:

-- This works, the problem is with the Address table...
insert into Person(name)
select name from Customer

-- This looks good but does not work because name is not unique.
insert into Address(person_id, address)
select p.person_id, c.address
from Customer c join Person p on c.name = p.name
Источник

2 ответа

Решение

Нет способа сделать это, как вы объясняете, потому что вы потеряли значение scope_identity() каждой строки первой вставки.

Обходным путем может быть добавление полей первичного ключа Customer в таблицу Person, а затем объединение второй вставки с этими полями:

перед вставкой создайте поле customerID для Person

alter table Person add customerID int null;

потом навальные вставки:

-- inserting customerID
insert into Person(name, customerID)
select name, customerID from Customer

-- joining on customerID.
insert into Address(person_id, address)
select p.person_id, c.address
  from Customer c 
  join Person p on c.customerID = p.customerID

после этого вы можете удалить поле customerID из таблицы Person:

alter table Person drop column customerID
Источник

Оставив это здесь для попутчика Google, который находит этот пост, как я.

Я нашел это решение, и оно, кажется, работает отлично, и не требует каких-либо шуточных изменений схемы: https://dba.stackexchange.com/questions/160210/splitting-data-into-two-tables-in-one-go

Они используют MERGE оператор для выполнения начальной вставки в первую таблицу (таблицу, которая генерирует идентификатор, который будет использоваться везде). Причина, по которой он использует MERGE утверждение, потому что это позволяет вам использовать OUTPUT оператор, который можно использовать для вывода как нового значения идентификатора, так и значения идентификатора из исходной таблицы (в отличие от использования OUTPUT утверждение по стандарту INSERT что не позволяет выводить идентичность исходных таблиц). Вы можете вставить эти выходные данные в таблицу сопоставления и использовать эту таблицу сопоставления для выполнения второй вставки.

Вот мой пример кода для решения:

------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Set up sample schema and data
------------------------------------------------------------------------------
--Source Data
IF OBJECT_ID('dbo.tmp1') IS NOT NULL DROP TABLE dbo.tmp1 --SELECT * FROM dbo.tmp1
CREATE TABLE dbo.tmp1 (tmp1ID INT IDENTITY(1,1), Col1 CHAR(1) NOT NULL, Col2 CHAR(1) NOT NULL, Col3 CHAR(1) NOT NULL, Col4 CHAR(1) NOT NULL, Col5 CHAR(1) NOT NULL, Col6 CHAR(1) NOT NULL)

INSERT INTO dbo.tmp1 (Col1, Col2, Col3, Col4, Col5, Col6)
SELECT x.c1, x.c2, x.c3, x.c4, x.c5, x.c6
FROM (VALUES ('A','B','C','D','E','F'),
             ('G','H','I','J','K','L'),
             ('M','N','O','P','Q','R')
) x(c1,c2,c3,c4,c5,c6)

IF OBJECT_ID('dbo.tmp3') IS NOT NULL DROP TABLE dbo.tmp3 --SELECT * FROM dbo.tmp3
IF OBJECT_ID('dbo.tmp2') IS NOT NULL DROP TABLE dbo.tmp2 --SELECT * FROM dbo.tmp2

--Taget tables to split into
CREATE TABLE dbo.tmp2 (
      tmp2ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_tmp2 PRIMARY KEY CLUSTERED (tmp2ID ASC)
    , Col1 CHAR(1) NOT NULL
    , Col2 CHAR(1) NOT NULL
    , Col3 CHAR(1) NOT NULL
)

CREATE TABLE dbo.tmp3 (
      tmp2ID INT NOT NULL
    , Col4 CHAR(1) NOT NULL
    , Col5 CHAR(1) NOT NULL
    , Col6 CHAR(1) NOT NULL
    , CONSTRAINT FK_tmp3_tmp2ID FOREIGN KEY(tmp2ID) REFERENCES dbo.tmp2 (tmp2ID)
)
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Split data into two tables
------------------------------------------------------------------------------
DECLARE @Mapping TABLE (tmp1ID INT NOT NULL, tmp2ID INT NOT NULL);

--Use merge statment to output the source data PK as well as the newly inserted identity to generate a mapping table
MERGE INTO dbo.tmp2 AS tgt
USING dbo.tmp1 AS src ON (1=0)
WHEN NOT MATCHED THEN
    INSERT (    Col1,     Col2,     Col3)
    VALUES (src.Col1, src.Col2, src.Col3)
OUTPUT src.tmp1ID, Inserted.tmp2ID INTO @Mapping (tmp1ID, tmp2ID);

--Use the mapping table to insert the split data into the second table
INSERT INTO dbo.tmp3 (tmp2ID, Col4, Col5, Col6)
SELECT t2.tmp2ID, t1.Col4, t1.Col5, t1.Col6
FROM dbo.tmp2 t2
    JOIN @Mapping m ON m.tmp2ID = t2.tmp2ID
    JOIN dbo.tmp1 t1 ON t1.tmp1ID = m.tmp1ID

SELECT tmp2ID, Col1, Col2, Col3 FROM dbo.tmp2
SELECT tmp2ID, Col4, Col5, Col6 FROM dbo.tmp3
------------------------------------------------------------------------------

------------------------------------------------------------------------------
-- Clean up
------------------------------------------------------------------------------
DROP TABLE dbo.tmp1
DROP TABLE dbo.tmp3
DROP TABLE dbo.tmp2
------------------------------------------------------------------------------

------------------------------------------------------------------------------
GO
Источник

Лучше, чтобы вы создали какое-то поле уникальных типов в обеих таблицах, связанных с ними. В противном случае вы хотите присоединиться, так как у вас нет уникального поля для условия.

Источник
Другие вопросы по тегам