Копирование реляционных данных из базы данных в базу данных

Отбросьте подход XML и используйте мастер импорта / SSIS.

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

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

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

declare @xml xml 
set @xml='<People Key = "1" FirstName = "Bob" LastName = "Smith">
  <PeopleAddresses PeopleKey = "1" AddressesKey = "1">
    <Addresses Key = "1" Street = "123 Main" City = "St Louis" State = "MO" ZIP = "12345" />
  </PeopleAddresses>
</People>
<People Key = "2" FirstName = "Harry" LastName = "Jones">
  <PeopleAddresses PeopleKey = "2" AddressesKey = "2">
    <Addresses Key = "2" Street = "555 E 5th St" City = "Chicago" State = "IL" ZIP = "23456" />
  </PeopleAddresses>
</People>
<People Key = "3" FirstName = "Sally" LastName = "Smith">
  <PeopleAddresses PeopleKey = "3" AddressesKey = "1">
    <Addresses Key = "1" Street = "123 Main" City = "St Louis" State = "MO" ZIP = "12345" />
  </PeopleAddresses>
</People>
<People Key = "4" FirstName = "Sara" LastName = "Jones">
  <PeopleAddresses PeopleKey = "4" AddressesKey = "2">
    <Addresses Key = "2" Street = "555 E 5th St" City = "Chicago" State = "IL" ZIP = "23456" />
  </PeopleAddresses>
</People>
'

select t.b.value('./@Key', 'int') PeopleKey,
    t.b.value('./@FirstName', 'nvarchar(50)') FirstName,
    t.b.value('./@LastName', 'nvarchar(50)') LastName
from @xml.nodes('//People') t(b)

select t.b.value('../../@Key', 'int') PeopleKey,
    t.b.value('./@Street', 'nvarchar(50)') Street,
    t.b.value('./@City', 'nvarchar(50)') City,
    t.b.value('./@State', 'char(2)') [State],
    t.b.value('./@Zip', 'char(5)') Zip
from 
@xml.nodes('//Addresses') t(b)

Это берет узлы из XML и анализирует данные. Чтобы получить реляционный идентификатор от людей, мы используем ../../ для продвижения вверх по цепочке.

Безусловно, самый простой способ - это средство сравнения данных SQL от Red Gate. Вы можете настроить его так, чтобы он делал то, что вы описали, через минуту или две.

Мне тоже нравятся SQL Compare и Data Compare от Red Gate, но, насколько я могу судить, они не соответствуют его требованиям к изменению первичных ключей.

Если перекрестные запросы к базе данных / связанные серверы являются вариантом, вы можете сделать это с помощью хранимой процедуры, которая копирует записи из родительского / дочернего в БД A во временные таблицы в БД B, а затем добавляет столбец для нового первичного ключа во временную дочернюю таблицу которые вы обновите после вставки заголовков.

У меня вопрос: если у записей нет одного и того же первичного ключа, как узнать, новая ли это запись? Есть ли другой ключ-кандидат? Если это новые таблицы, почему они не могут иметь одинаковый первичный ключ?

Я создал то же самое с набором хранимых процедур.

База данных B будет иметь свои собственные первичные ключи, но хранить первичные ключи базы данных A для целей отладки. Это означает, что у меня может быть более одной базы данных A!

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

А из C# легко вызвать хранимую процедуру.

Я бы написал его в хранимой процедуре, используя вставки для тяжелой работы. Ваш код возьмет PK из таблицы A (предположительно через @@ Scope_Identity) - я предполагаю, что PK для таблицы A является полем Identity?

Вы можете использовать временные таблицы, курсоры или можете предпочесть среду CLR - она ​​может подойти для такого рода операций.

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

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

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

Вставьте данные родительской таблицы в место назначения и используйте предложение ВЫХОД, чтобы захватить новые идентификаторы и вставить их со старыми идентификаторами во временную таблицу. ПРИМЕЧАНИЕ. Использование предложения output является эффективным и позволяет выполнять массовую вставку без циклического перебора каждой вставляемой записи.

Вставьте данные дочерней таблицы. Присоединитесь к временной таблице, чтобы получить новый требуемый внешний ключ.

Вышеупомянутый процесс может быть выполнен с использованием сценария T-SQL, кода C# или SSIS. Я бы предпочел SSIS.

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

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

Я думаю, что, скорее всего, я буду использовать типизированные наборы данных. Это не будет обобщенное решение; нам придется регенерировать их, если какая-либо из таблиц изменится. Но судя по тому, что мне сказали, это не проблема; ожидается, что таблицы не сильно изменятся.

Наборы данных позволят достаточно легко просматривать данные в иерархическом порядке и обновлять PK из базы данных после вставки.

Имея дело с аналогичными задачами, я просто создал набор хранимых процедур для выполнения этой работы.

Поскольку указанная вами задача довольно нестандартная, вы вряд ли найдете «готовое к использованию» решение.

Просто чтобы дать вам несколько советов:

• Если базы данных находятся на разных серверах, используйте связанные серверы, чтобы вы могли получить доступ как к исходным, так и к целевым таблицам просто через TSQL.

В хранимой процедуре:

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

Нет необходимости в курсорах и т. д., Просто сохраните немедленные результаты во временной таблице (или в табличной переменной, если работаете в одной хранимой процедуре).

Этот подход сработал для меня очень хорошо.

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

Дайте мне знать, если это поможет.

Я думаю, что утилита SQL Server tablediff.exe может быть тем, что вы ищете.

См. Также эта ветка.