Ни раз возникала задача переноса данных из одной БД в другую. Как правило, это Контакты, Контрагенты, Задачи. Задача усложнялась тем, что БД могут быть не совсем похожи по структурам. Приходилось писать insert-select конструкции с перечислением всех полей, которые необходимо перенести. Дабы упростить, а главное ускорить сей процесс был написал скрипт, которому собственно и посвящен этот пост.
Собственно, что делает сам скрипт. Идет по списку таблиц, указанных пользователем, проверяет соответствие полей в таблицах обеих БД, формирует запрос и выполняет его.
Возможно эти труды будут кому-то полезны.
Скрипт:
declare @syncTablesNames TABLE (TableName varchar(50));
declare @sourceDBName varchar(50),
@tableName varchar(50),
@columnName varchar(50),
@sourceColumnExists bit,
@insertSQLText nvarchar(MAX),
@selectSQLText nvarchar(MAX),
@columnExistsSQLText nvarchar(MAX),
@idExistsCondition nvarchar(MAX),
@resultSQLText nvarchar(MAX);
SET @sourceDBName = 'SourceDataBaseName';
-- set tables names
INSERT INTO @syncTablesNames
SELECT 'tbl_Contact' UNION
SELECT 'tbl_Account';
declare tableNameCursor cursor LOCAL FAST_FORWARD FOR SELECT * FROM @syncTablesNames;
open tableNameCursor;
fetch next FROM tableNameCursor INTO @tableName
WHILE (@@fetch_status = 0)
BEGIN
SET @insertSQLText = 'insert into ' + @tableName + '(';
SET @selectSQLText = 'select ';
SET @idExistsCondition = ' where not ID in (select ID from ' +
@tableName + ');';
--Disable Table Constraint
exec ('alter table ' + @tableName + ' nocheck constraint all; ');
declare columnNameCursor cursor LOCAL FAST_FORWARD FOR SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = @tableName;
open columnNameCursor;
fetch next FROM columnNameCursor INTO @columnName;
while (@@fetch_status = 0)
begin
SET @columnExistsSQLText = 'if exists (select COLUMN_NAME ' +
'from ' + @sourceDBName +
'.information_schema.columns ' +
'where TABLE_NAME = ''' +
@tableName + ''' AND ' +
'COLUMN_NAME = ''' +
@columnName + ''') ' +
'set @sourceColumnExists = 1 ' +
'else ' +
'set @sourceColumnExists = 0 ';
--check remote table column exists
exec sp_executesql @columnExistsSQLText,
N'@sourceColumnExists bit out',
@sourceColumnExists = @sourceColumnExists output
--include column into select-insert statment
IF (@sourceColumnExists = 1)
begin
SET @insertSQLText = @insertSQLText + @columnName + ', ';
SET @selectSQLText = @selectSQLText + @columnName + ', ';
end;
fetch next FROM columnNameCursor INTO @columnName;
end;
close columnNameCursor;
deallocate columnNameCursor;
SET @insertSQLText = substring(@insertSQLText, 1, len(@insertSQLText) - 2);
SET @selectSQLText = substring(@selectSQLText, 1, len(@selectSQLText) - 2);
SET @insertSQLText = @insertSQLText + ') ';
SET @selectSQLText = @selectSQLText + ' from ' + @sourceDBName + '.dbo.' +
@tableName + @idExistsCondition;
--result select-insert statment
SET @resultSQLText = @insertSQLText + @selectSQLText;
exec (@resultSQLText);
fetch next FROM tableNameCursor INTO @tableName
END;
close tableNameCursor;
deallocate tableNameCursor;
--Enable Table Constraint
declare tableNameCursor cursor LOCAL FAST_FORWARD FOR SELECT * FROM @syncTablesNames;
open tableNameCursor;
fetch next FROM tableNameCursor INTO @tableName;
WHILE (@@fetch_status = 0)
BEGIN
exec ('alter table ' + @tableName + ' check constraint all; ');
fetch next FROM tableNameCursor INTO @tableName;
END;
close tableNameCursor;
deallocate tableNameCursor;
rollback
Юрий, в принципе очень добротный скрипт, но есть пара замечаний:
1. Я бы обрамлял идентификаторы в []
2.
"Доленко Юрий" написал:TableName varchar(50)
Есть системный тип - sysname, он лучше подходит для идентификаторов таблиц, колонок
3.
"Доленко Юрий" написал:--Disable Table Constraint
exec ('alter table ' + @tableName + ' nocheck constraint all; ');
Я бы еще и триггера отключал, ну и ";" - лишнее
4.
"Доленко Юрий" написал:rollback
Это как говорится: "Счастливой отладки:)"?
В принципе это нужный скрипт (если разговор идет только в рамках MSSQL). Сам столкнулся с такой проблемой, но решил ее стандартными средствами. Нужно было перенести данные с 3.2.0 FB в 3.3.2 MSSQL.В версии 3.3.2 появилась возможность выгружать и загружать данные таблиц (wnd_DataManager). Эти сервисы переносятся и в 3.2 (была такая необходимость :)).
Во всяком случае два варианта лучше чем один :smile:
5.
"Доленко Юрий" написал:@insertSQLText nvarchar(MAX),
К сожалению для mssql 2000 не пойдет... Для него можно пойти путем как сделано в ХП - tsp_AdministratedByRecords
Александр, большое спасибо за советы, обязательно учту. (я рассчитывал черпнуть чего полезного из этого поста :) )
"Евгений Либин" написал:В версии 3.3.2 появилась возможность выгружать и загружать данные таблиц (wnd_DataManager).
Как же все-таки полезно писать блоги ). Относительно давно работаю с террасофтом, но о такое полезной утилитке не слышал. Действительно, выгружает/загружает xml, хотя судя по коду не отключаются ни триггера, ни constraint и грузится только по одной таблице. Могут ли быть нарушения целостности в процессе?
Нет, она не отключает не триггеры и констреинты. Можно выгружать сразу несколько таблиц. Я делал так, сначала выгрузил все справочники и загружал полученный скрипт пока на уйдут ошибки о несуществующих данных в связанных справочниках :lol:
После этого перешел к контактам и контрагентам. Тут как всегда философия "что было раньше контакт или контрагент?". В общем, сделал сначала загрузку контактов без ссылки на контрагента, а потом загрузил контрагентов и еще раз загрузил контакты.:lol:
Тоже была подобная задача по перебросу из FB в MS SQL. Есть такая штука как EMS SQL Managment studio для фаерберда. Там можно выгружать sql-скрипт(вида insert ... values) таблицы в синтаксисе t-sql(в нашем случае), при чем выбирать поля, которые будут фигурировать в запросе.
Потом можно отключить ограничения и просто выполнить каждый по одному разу...
DataManager позволяет выгрузить несколько таблиц в один файл и соответственно загрузить. Причиной его создания было то, что разработчики решили упростить перенос создаваемого в процессе разработки наполнения с базы разработки на живую базу, это как правило были записи справочников.
В вашем скрипте есть пара неточностей, которые мешают нормально загружать данные.
в скрипте scr_DataManagerUtils
процедура SetDatasetValuesByDataRowNode
строку
if (DataRowNode.GetAttributeAsStr(DataField.Name, '') == 'IsNull') {
надо заменить на
if (DataRowNode.GetAttributeAsStr(DataField.Name, 'IsNull') == 'IsNull') {
процедура LoadDataFromDataRowNodeToDataset
строку
Log.Write(2, "Ошибка загрузки: " + e.Message)
надо заменить на
Log.Write(2, "Ошибка загрузки: " + e.message)
Если вы посчитаете мои замечания корректными - то включите их в базовую версию
Евгений, спасибо за замечания.
"Евгений Либин" написал:надо заменить наif (DataRowNode.GetAttributeAsStr(DataField.Name, 'IsNull') == 'IsNull'){
В таком случае выйдет, что если в базе, в которую загружают, есть поле, которого не было в базе, из которой выгрузили данные, и данные обновляются (например, повторная загрузка), а значение этого поля было заполнено, то оно перетрется. Не думаю, что это всегда правильно. Думаю, правильнее будет дописать еще одно условие:
if (DataRowNode.GetAttributeAsStr(DataField.Name, '') == '') { continue; }
Четко подмечено :twisted:
Т.е. в результате мы получим такой код?
if (DataRowNode.GetAttributeAsStr(DataField.Name, '') == 'IsNull') { DataField.Value = null; continue; } if (DataRowNode.GetAttributeAsStr(DataField.Name, '') == '') { continue; }
Надо бы добавить обработку загрузки контрактов-контрагентов
Да, думаю, такой, только вынести бы в отдельную переменную
var DataFieldNameAttr = DataRowNode.GetAttributeAsStr(DataField.Name, '');
"Евгений Либин" написал:Надо бы добавить обработку загрузки контрактов-контрагентов
Какую именно обработку Вы имеете ввиду?
Если переносить в чистую базу раздел контакты, то без контрагентов контакты не зальются (Поле контрагент заполнено), а контрагента нельзя залить пока не залиты контакты (поле основной контакт). Вот и получается что нужно сделать признак вида "сохранять контакт при отсутствующем контрагенте" и в коде проверять: если грузим контакты, то проверить наличие контрагента и тд.... Хотя, наверно, у вас найдется более изящное решение этой проблемы :wink:
Теперь я чувствую себя просто обязанной придумать альтернативный вариант :) Не уверена, что изящно, но можно удалять связи контрагента с контактом и контакта с контрагентом в таблицах, а после импорта создавать заново. Тогда должен быть более сложный выбор таблиц, потом полей, на которые не должна срабатывать проверка связи. Зато это будет в один проход.
"Раловец Ольга" написал:придумать альтернативный вариант :)
Так вот же он) в первом посте. Буквально вчера этим скриптом за две минуты перекинул Контакты, контрагенты, средства связи(обоих разделов) и задачи (и группы всех разделов), причем структуры таблиц несколько отличались.
К слову, с группами без отключения ограничений будет крайне неприятно работать ибо связь сама на себя.
"Доленко Юрий" написал:Раловец Ольга пишет:
придумать альтернативный вариант :)
Так вот же он) в первом посте.
Юрий, отличный вариант :) Просто мы с Евгением уже начали обсуждение в контексте Datamanager.
К сожаление скрипт не смотрел, но идея с переносом ссылок должна быть такой:
1. Переносим все данные без внешних ссылок
2. Обновляем все записи, где проставляем ссылки, благо у нас GUID, и тем более все ссылки уже физически присутствуют в БД
Есть вопрос со ссылками, когда они обязательны на уровне БД. Тогда надо проверить, если поле ссылка обязательное, то в таблицу справочник вносим мнимую запись скажем нулевым GUID или с каким-то признаком в Name (как правило такое поле есть) - например со значением "Импорт. Удалить после привязки ссылок". И тогда во все записи с таким и полями вставляем ссылку на мнимую запись и обновляем ее после переноса всех записей.