Перенос строк из одной таблицы в другую в SQL Server

Быстрый ответ

Рассмотрим пример эффективного копирования данных:

-- Выбираем строки для переноса
INSERT INTO новая_таблица (колонка1, колонка2)
SELECT колонка1, колонка2
FROM старая_таблица
WHERE условие; 

-- Удаляем эти же строки из исходной таблицы
DELETE FROM старая_таблица 
WHERE условие;

Подставьте значения новая_таблица, старая_таблица, колонка1, колонка2 и условие в соответствии с вашими требованиями. Этот метод помогает сохранять целостность данных, перенося определенные строки.

Ситуации, когда сочетание INSERT и DELETE не подходит

Иногда удаление строк неприменимо, например, в случаях поддержания историчности данных. В таких ситуациях строки отмечаются как перенесенные:

-- Проверяем неперенесенные строки
UPDATE старая_таблица
SET is_transferred = 1
WHERE условие
  AND NOT EXISTS (SELECT 1 FROM новая_таблица WHERE старая_таблица.id = новая_таблица.id);

-- Затем переносим неотмеченные строки
INSERT INTO новая_таблица (колонка1, колонка2)
SELECT колонка1, колонка2
FROM старая_таблица
WHERE условие
  AND is_transferred = 0;

Столбец is_transferred служит индикатором факта переноса строки.

Метод "убить двух зайцев одним выстрелом" в SQL Server

В SQL Server можно использовать команду OUTPUT для экономии времени и ресурсов:

DELETE FROM старая_таблица
OUTPUT DELETED.колонка1, DELETED.колонка2 INTO новая_таблица
WHERE условие;

В результате, записи удаляются из старая_таблица и сразу переносятся в новая_таблица.

Сохранение первичного ключа

Для сохранения значений первичного ключа при переносе используется IDENTITY_INSERT:

-- Включаем возможность программного указания значений для столбца identity
SET IDENTITY_INSERT новая_таблица ON;

INSERT INTO новая_таблица (id, колонка1, колонка2)
SELECT id, колонка1, колонка2
FROM старая_таблица
WHERE условие;

-- Отключаем эту возможность
SET IDENTITY_INSERT новая_таблица OFF;

Не забывайте включать IDENTITY_INSERT в состояние ON перед вставкой данных и отключать его OFF после завершения операции.

Адаптация под специфику различных СУБД

У каждой системы управления базами данных есть свой уникальный синтаксис и набор возможностей.

SQL Server

-- Манипулируем данными в Table
DELETE FROM старая_таблица
OUTPUT DELETED.* INTO новая_таблица
WHERE условие;

Подробнее об этом расскажет наш спикер на видео

MySQL

-- В MySQL требуется транзакция для обеспечения целостности
START TRANSACTION;

INSERT INTO новая_таблица
SELECT * FROM старая_таблица
WHERE условие;

DELETE FROM старая_таблица
WHERE условие;

COMMIT;

PostgreSQL

-- PostgreSQL использует подзапрос с ключевым словом RETURNING
WITH moved_rows AS (
    DELETE FROM старая_таблица
    WHERE условие
    RETURNING колонка1, колонка2
)
INSERT INTO новая_таблица
SELECT колонка1, колонка2
FROM moved_rows;

Визуализация

Предположим, что требуется перенести книги из одной "библиотеки" в другую:

Текущая библиотека (🗃️ Старая_Таблица):

- 📗 SQL для начинающих
- 📘 Продвинутый SQL
- 📙 Рецепты SQL

Выполняем перенос вот так:

INSERT INTO Новая_Таблица SELECT * FROM Старая_Таблица;

Теперь в новой библиотеке (🗃️ Новая_Таблица):

- 📗 SQL для начинающих
- 📘 Продвинутый SQL
- 📙 Рецепты SQL

✨ Мир знаний обновлен!

Таковым образом, данные (или книги) были перемещены из одного хранилища в другое.

Предотвращение проблем с условиями гонки

Условия гонки могут возникнуть при параллельном переносе данных. Воспользуйтесь сериализуемыми транзакциями или блокировками строк для сохранения консистентности данных:

-- Начинаем транзакцию
BEGIN TRAN;

-- Блокируем данные для переноса
SELECT *
FROM старая_таблица WITH (UPDLOCK, HOLDLOCK)
WHERE условие;

-- Выполняем перенос и удаляем строки
INSERT INTO новая_таблица (колонка1, колонка2)
SELECT колонка1, колонка2
FROM старая_таблица
WHERE условие;

DELETE FROM старая_таблица
WHERE условие;

-- Завершаем транзакцию
COMMIT TRAN;

Блокировки UPDLOCK и HOLDLOCK исключают возможность вмешательства других транзакций в процесс до его завершения.

Решение распространенных проблем

В ходе переноса могут возникнуть некоторые проблемы:

• Конфликты данных: Первичные ключи и уникальные индексы могут вызвать ошибки при вставке дублирующих записей.
• Длительные транзакции: Могут отрицательно повлиять на производительность. Разделение процесса на меньшие части может помочь.
• Ограничения внешних ключей: При наличии зависимостей в целевой таблице может потребоваться временное отключение ограничений или предварительное перемещение связанных данных.

Продвинутые приемы работы с SQL

Некоторые полезные советы:

• Тестируйте запросы на тестовых базах данных перед использованием их на продакшене.
• Создавайте резервные копии перед проведением массовых изменений.
• Помните о влиянии на производительность: перенос больших объемов данных может привести к блокировке таблиц и снижению доступности системы.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 13.2.7.1 Выражение INSERT ... SELECT – официальная документация MySQL по инструкции INSERT ... SELECT.
2. INSERT – документация Oracle по INSERT ALL.
3. Обсуждение: клиент C# и сервер Java через SSL – Stack Overflow – обсуждения и опыт использования передачи данных через интернет на Stack Overflow.
4. Управление расширенными событиями SQL Server в Management Studio – статья о методах управления данными в SQL Server.

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