TSQL: UPDATE с INSERT IN SELECT FROM

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

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

например, я выбираю из MV5.Posts и вставляет в MV6.Posts. После вставки я получаю идентификатор новой строки в MV6.Posts и обновляю ее в старом поле MV5.Posts.MV6ID.

Есть ли способ сделать это UPDATE через INSERT INTO SELECT FROM, поэтому мне не нужно обрабатывать каждую запись вручную? Я использую SQL Server 2005, dev edition.

Ответ 1

Ключ миграции - это сделать несколько вещей: Во-первых, не делайте ничего без текущей резервной копии. Во-вторых, если ключи будут меняться, вам необходимо сохранить как старые, так и новые в новой структуре, по крайней мере, временно (постоянно, если поле ключа открыто для пользователей, потому что они могут искать его для получения старых записей).

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

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

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

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

Update t2
set fkfield = newkey
from table2 t2
join table1 t1 on t1.oldkey = t2.fkfield

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

Ответ 2

Вероятно, самым простым способом было бы добавить столбец на MV6.Posts для oldId, а затем вставить все записи из старой таблицы в новую таблицу. Наконец, обновите старую таблицу, сопоставляющую oldId в новой таблице, с чем-то вроде:

UPDATE mv5.posts
SET newid = n.id
FROM mv5.posts o, mv6.posts n 
WHERE o.id = n.oldid

После этого вы можете очистить столбец oldId после этого.

Ответ 3

Лучшее, что вы можете сделать, что я знаю, это предложение output. Предполагая, что у вас есть SQL 2005 или 2008.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

Для обновления исходной таблицы все равно потребуется второй проход; однако это может помочь сделать вашу логику проще. Вам нужно обновить исходную таблицу? Вы можете просто сохранить новый идентификатор в третьей таблице перекрестных ссылок.

Ответ 4

Хех. Я помню, как это делалось в процессе миграции.

Помещение old_id в новую таблицу упрощает обновление, вы можете просто сделать insert into newtable select ... from oldtable, и последующую "сшивку" записей проще. В "стежке" вы либо обновите внешние ключи дочерних таблиц во вставке, выполнив подзапрос нового родителя (insert into newchild select ... (select id from new_parent where old_id = oldchild.fk) as fk, ... from oldchild), либо вставьте дочерние элементы и сделайте отдельное обновление, чтобы исправить внешние ключи.

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

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

В самом деле, если вы правильно определили внешние ключи, вы можете использовать systables/information-schema для создания своих операторов вставки.

Ответ 5

Есть ли способ сделать это UPDATE через INSERT INTO SELECT FROM, поэтому мне не нужно обрабатывать каждую запись вручную?

Поскольку вы не хотите делать это вручную, но автоматически, создайте триггер на MV6.Posts, чтобы UPDATE автоматически включался MV5.Posts при вставке в MV6.Posts.

И ваш триггер может выглядеть примерно так:

create trigger trg_MV6Posts
on MV6.Posts
after insert
as
begin
    set identity_insert MV5.Posts on

    update  MV5.Posts
    set ID = I.ID
    from    inserted I

    set identity_insert MV5.Posts off
end

Ответ 6

AFAIK, вы не можете обновлять две разные таблицы с помощью одного оператора sql

Однако вы можете использовать триггеры для достижения того, что вы хотите сделать.

Ответ 7

Сделать столбец в MV6.Post.OldMV5Id

сделать вставить в MV6.Post выберите.. из MV5.Post

затем сделайте обновление MV5.Post.MV6ID