Ни раз возникала задача переноса данных из одной БД в другую. Как правило, это Контакты, Контрагенты, Задачи. Задача усложнялась тем, что БД могут быть не совсем похожи по структурам. Приходилось писать insert-select конструкции с перечислением всех полей, которые необходимо перенести. Дабы упростить, а главное ускорить сей процесс был написал скрипт, которому собственно и посвящен этот пост.
Собственно, что делает сам скрипт. Идет по списку таблиц, указанных пользователем, проверяет соответствие полей в таблицах обеих БД, формирует запрос и выполняет его.
Возможно эти труды будут кому-то полезны.
Скрипт:
begin transaction
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
