Скопируйте наборы данных с n: m-отношением

Я хотел бы использовать единственный SQL-оператор

insert into T (...) select ... from T where ... 

чтобы скопировать множество наборов данных. Моя проблема в том, что существуют N: M-отношения из таблицы T в другие таблицы, и они также должны быть скопированы. Как я могу это сделать, если я не знаю, какой исходный набор данных принадлежит скопированному набору данных? Позвольте мне продемонстрировать на примере.

Содержимое базы данных до:

Таблица T :

 ID | COL1 | COL2 ----------------- 1 | A | B 2 | C | D 

N: таблица ссылок M-таблицы U из таблицы T (таблица U не показана):

 T | U --------- 1 | 100 1 | 101 2 | 100 2 | 102 

Моя операция копирования, где [???] является той частью, которую я не знаю:

 insert into T (COL1, COL2) select COL1, COL2 from T insert into NM (T, U) select [???] 

Содержимое базы данных после:

Таблица T :

 ID | COL1 | COL2 ----------------- 1 | A | B 2 | C | D 3 | A | B 4 | C | D 

Н: М-таблица:

 T | U --------- 1 | 100 1 | 101 2 | 100 2 | 102 3 | 100 3 | 101 4 | 100 4 | 102 

Обратите внимание:

  • У меня есть тысячи наборов данных (не только двух)
  • Я хочу использовать «insert … select», чтобы получить лучшую производительность

Если вам посчастливилось запустить текущий PostgreSQL 9.1 , есть элегантное и быстрое решение с одной командой, использующей новые модифицирующие данные CTE .

Нет такой удачи в MySQL, которая не поддерживает Common Table Expressions (CTE) , не говоря уже о модифицировании данных CTE.

Предполагая, что (col1, col2) изначально уникальна:

Запрос 1

  • В этом случае вы можете легко выбрать произвольные фрагменты из таблицы.
  • Никакие порядковые номера для t.id не будут потрачены впустую.

 WITH s AS ( SELECT id, col1, col2 FROM t -- WHERE some condition ) ,i AS ( INSERT INTO t (col1, col2) SELECT col1, col2 -- I gather from comments that id is a serial column FROM s RETURNING id, col1, col2 ) INSERT INTO tu (t, u) SELECT i.id, tu.u FROM tu JOIN s ON tu.t = s.id JOIN i USING (col1, col2); 

Если (col1, col2) не является уникальным , я вижу два других способа:

Запрос 2

  • Используйте оконную функцию row_number() чтобы сделать уникальные строки уникальными.
  • INSERT строк без отверстий в пространстве t.id как в запросе выше.

 WITH s AS ( SELECT id, col1, col2 , row_number() OVER (PARTITION BY col1, col2) AS rn FROM t -- WHERE some condition ) ,i AS ( INSERT INTO t (col1, col2) SELECT col1, col2 FROM s RETURNING id, col1, col2 ) ,r AS ( SELECT * , row_number() OVER (PARTITION BY col1, col2) AS rn FROM i ) INSERT INTO tu (t, u) SELECT r.id, tu.u FROM r JOIN s USING (col1, col2, rn) -- match exactly one id per row JOIN tu ON tu.t = s.id; 

Запрос 3

  • Это основано на той же идее, что уже предоставлен @ypercube, но все в одном запросе.
  • Если в текущем t.id есть пробелы в t.id , порядковые номера будут соответственно t.id для новых строк.
  • Не забудьте сбросить свою последовательность за пределы нового максимума или вы получите повторяющиеся ключевые ошибки для новых вставок в t которые выводят значение по умолчанию для id из последовательности. Я включил это как последний шаг в команду. Самый быстрый и безопасный способ.

 WITH s AS ( SELECT max(id) AS max_id FROM t ) ,i AS ( INSERT INTO t (id, col1, col2) SELECT id + s.max_id, col1, col2 FROM t, s ) ,j AS ( INSERT INTO tu (t, u) SELECT tu.t + s.max_id, tu.u FROM tu, s ) SELECT setval('t_id_seq', s.max_id + s.max_id) FROM s; 

Подробная информация о setval () в руководстве.

Испытательная установка

Для быстрого теста.

 CREATE TEMP TABLE t (id serial primary key, col1 text, col2 text); INSERT INTO t (col1, col2) VALUES ('A', 'B') ,('C', 'D'); CREATE TEMP TABLE tu (t int, u int); INSERT INTO tu VALUES (1, 100) ,(1, 101) ,(2, 100) ,(2, 102); SELECT * FROM t; SELECT * FROM tu; 

Недавно был несколько схожий вопрос , где я дал несколько схожий ответ. Плюс альтернативы для версии 8.3 без CTE и функций окна.

Шаг 1. Заблокируйте (обе) таблицы или убедитесь, что работает только этот скрипт. Отключить проверки FK.

Шаг 2. Используйте эти два оператора INSERT в следующем порядке:

 INSERT INTO NM (T, U) SELECT T + maxID, U FROM NM CROSS JOIN ( SELECT MAX(ID) AS maxID FROM T ) AS m INSERT INTO T (ID, COL1, COL2) SELECT ID+maxID, COL1, COL2 FROM T CROSS JOIN ( SELECT MAX(ID) AS maxID FROM T ) AS m - INSERT INTO NM (T, U) SELECT T + maxID, U FROM NM CROSS JOIN ( SELECT MAX(ID) AS maxID FROM T ) AS m INSERT INTO T (ID, COL1, COL2) SELECT ID+maxID, COL1, COL2 FROM T CROSS JOIN ( SELECT MAX(ID) AS maxID FROM T ) AS m 

Шаг 3. Перезапустите FK.