Записи SQL MOVE в другую таблицу

У меня есть ряд функций, которые MOVE записывает из одной таблицы в другую (как правило, для архивирования данных) и задается вопросом, существует ли "лучшая практика" для этого или более эффективный метод, чем я сейчас используя.

На данный момент я запускаю что-то вроде:

INSERT INTO archive_table
SELECT [ROWID], [COL1], [COL2]
FROM live_table
WHERE <criteria>
DELETE FROM live_table
WHERE [ROWID] IN
(
 SELECT [ROWID] FROM archive_table
)
</criteria>

Это также вызывает предупреждение о программном обеспечении производительности SQL, которое может вызвать подавление индекса и ухудшение производительности; из-за выполнения SCAN, а не SEEK.

Стоит добавить, что archive_table - это точная копия live_table, за исключением того, что мы удалили идентификатор и первичный ключ из столбца [ROWID] и что эта таблица не используется в "живой" среде, другая чем вставлять старые данные, как описано.

[править]

Казалось бы, ответ от Alex дает действительно простое решение; комментарий об использовании триггера не разрешает проблему в этом случае, поскольку событие происходит через несколько дней, а критерии зависят от событий за этот период.

DELETE 
FROM live_table 
OUTPUT DELETED.* INTO archive_table
WHERE
3 ответа

Если вам нужно переместить большое количество записей из одной таблицы в другую, я предлагаю вам проверить возможность разделения вашей "активной таблицы". Каждый раз вы копируете данные из одного (или более) разделов в "таблицу достижений" и отбрасываете эти разделы. Это будет намного быстрее, чем удаление записей из таблицы "онлайн".


Ваш фрагмент кода не включает именованную транзакцию, которая ДОЛЖНА быть первым рассмотрением. Во-вторых, создайте переменную таблицы, таблицу temp или таблицу жестких дисков, которая будет использоваться для этапа. В таблице, предназначенной для разработки, должен быть указан столбец, идентичный типу данных, в столбце идентификации из вашей исходной таблицы и этот столбец должен быть проиндексирован. Третий дизайн TSQL для заполнения промежуточной таблицы, копирования строк из исходной таблицы в таблицу назначения на основе соединения между исходным и промежуточным, а затем удалить строки из исходной таблицы на основе того же соединения, которое перемещает данные в таблицу назначения. Ниже приведен рабочий образец

--test setup below
DECLARE @live_table table (rowid int identity (1,1) primary key clustered, col1 varchar(1), col2 varchar(2))
DECLARE @archive_table table (rowid int, col1 varchar(1), col2 varchar(2))
Insert @live_table (col1, col2)
Values 
('a','a'),
('a','a'),
('a','a'),
('a','a'),
('b','b')
--test setup above
BEGIN Transaction MoveData
 DECLARE @Staging table (ROWID int primary Key)
 Insert @Staging 
 SELECT lt.rowid
 FROM @live_table as lt
 WHERE lt.col1 = 'a'
 INSERT INTO @archive_table
 select lt.rowid, lt.col1, lt.col2
 FROM @live_table as lt
 inner join @Staging as s on lt.rowid = s.ROWID
 DELETE @live_table
 FROM @live_table as lt
 inner join @Staging as s on lt.rowid = s.ROWID
COMMIT Transaction MoveData
select * from @live_table
select * from @archive_table
select * from @Staging


Стоит добавить, что archive_table - это точная копия live_table, за исключением того, что мы удалили идентификатор и первичный ключ из столбца [ROWID] и что эта таблица не используется в "живой" среде, другая чем вставлять старые данные, как описано.

Я не могу определить, является ли причина, по которой вы удаляете первичный ключ из archive_table, потому, что вы ожидаете, что ROWID будет повторно использоваться в live_table или нет.

Если я правильно понимаю контекст ваших данных и что вы хотите архивировать дни после завершения данных, вы можете повысить производительность запроса, уменьшив/устранив сравнение строк, которые не будут существовать в live_table, В принципе, как только ROWID переместился из live_table в archive_table, нет причин искать его снова.

Примечание.. Предполагается, что ROWID не используются повторно в live_table и всегда увеличиваются.

INSERT INTO archive_table
SELECT [ROWID], [COL1], [COL2]
FROM live_table
WHERE <criteria>
DELETE FROM live_table
WHERE [ROWID] IN
(
 SELECT [ROWID] FROM archive_table WHERE [ROWID] >= (SELECT MIN(ROWID) FROM live_table)
)
</criteria>

Если ROWID повторно используется. Если в вашем наборе данных есть поле даты и времени, близкое к тому, когда запись была вживлена ​​или архивирована, ее можно использовать в качестве альтернативы ROWID. Это означало бы, что вы ищете только недавно архивированные строки для удаления из live_table, а не всего набора. Кроме того, кластеризованный индекс в файле archive_table может повысить производительность, так как данные будут физически упорядочены, когда вы смотрите только на хвост таблицы.

INSERT INTO archive_table
SELECT [ROWID], [COL1], [COL2]
FROM live_table
WHERE <criteria>
DELETE FROM live_table
WHERE [ROWID] IN
(
 SELECT [ROWID] FROM archive_table WHERE [somedate] >= DATEADD(dy,-30,GETDATE())
)
</criteria>

licensed under cc by-sa 3.0 with attribution.