====== ======
This document describes the process (and SQLs) used to copy all macro's and related macro actions from one database to another. The source macros and macro actions are all moved to the lowest possible IDs, just above any that are already in the target database. (The minimum ID assigned will be 100.) Please Note: If a macro or macro action with the same ID already exists, it will be assumed to be the same and it will not be imported.
WARNING: This article is for reference. The changes made will PERMANENTLY affect your data. This should only be carried out by a skilled implementer and SQL practitioner.
===== Concept =====
The idea of this approach is to use SQL queries to renumber and insert the relevant data from one table to another. The basic approach for each table will be:
- Create a temporary Database (ConvTemp) for all the working conversion tables
- [Optional] Restore a backup of the destination database
- Create a map table (containing TABLENAME, OLDID, NEWID) in the temp database
- For each table to Migrate:
* Load the map table
* Create a working copy of the table
* Compute the NewIDs
- ReMap each field in the database
- Insert the working records into the new database
If the existing database has a record with the same ID as the source database, the existing data will always take precedence.
Other Notes:
* Only fields found in **both** the source and destination tables are copied.
===== SQL =====
-- Merge Macros from a Dataset Using SQL
--
--------------------------------------------------------------------------------------
-- Section: Declare Variables and Create Databases
--
-- This section is used to
-- * Declare all variables the user needs to set
-- * Create the working database (ConvTemp)
-- * Restore the source database (if that option is set)
-- * Restore the destination database (if that option is set)
-- * Writes all user variables to a database table for other sections to use
--------------------------------------------------------------------------------------
-- Create the Temporary Database used to hold all Working Data
--
USE master;
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'ConvTemp')
begin
ALTER DATABASE ConvTemp SET single_user WITH rollback immediate; -- this will force all others users off!
DROP DATABASE ConvTemp;
end;
CREATE DATABASE ConvTemp;
go
DECLARE @SourceDatabase varchar(255);
DECLARE @DestDatabase varchar(255);
DECLARE @RestoreDestDatabase bit;
DECLARE @RestoreDestBackupFile varchar(255);
DECLARE @RestoreDestFilePath varchar(255);
DECLARE @RestoreSourceDatabase bit;
DECLARE @RestoreSourceBackupFile varchar(255);
DECLARE @RestoreSourceFilePath varchar(255);
DECLARE @S varchar(max);
SET @SourceDatabase = 'Scott_Latest_OOBData'; -- The name of the source database
SET @DestDatabase = 'Scott_Hansen_Updated'; -- The name of the target database
SET @RestoreDestDatabase = 0; -- Set this to 1 to delete the destination database and restore from backup
SET @RestoreDestBackupFile = 'D:\Data\SQL Data\Dev\Scott\HansenSigns11-13-2009backup.bak';
SET @RestoreDestFilePath = 'D:\Data\SQL Data\Dev\Scott\'; -- include trailing backslash
SET @RestoreSourceDatabase = 0; -- Set this to 1 to delete the source database and restore from backup
SET @RestoreSourceBackupFile = 'D:\DATA\SQL DATA\Dev\Scott\New OOB Dataset 2009-10-09.bak';
SET @RestoreSourceFilePath = 'D:\DATA\SQL DATA\Dev\Scott\'; -- include trailing backslash
use ConvTemp;
if exists(select * from ConvTemp.sys.objects where name = 'TestLog')
drop table ConvTemp.dbo.TestLog;
create table ConvTemp.dbo.TestLog (id int primary key identity, token varchar(50) not null, value varchar(max));
create index TestLog_TokenIndex on ConvTemp.dbo.TestLog (token);
-- Create Helper Functions to Store and Retrieve Data
if exists(select * from sys.objects where name = 'LogData')
Drop procedure LogData;
set @S = 'CREATE PROCEDURE LogData @Token varchar(50), @Value varchar(max) '+
'AS '+
'BEGIN '+
'SET NOCOUNT ON; '+
'DELETE FROM TestLog '+
'WHERE token = @Token; '+
'INSERT INTO TestLog (token, value) '+
'SELECT @Token, @Value '+
'END ';
execute (@S);
if exists(select * from sys.objects where name = 'GetData')
Drop Function GetData;
set @S = 'CREATE FUNCTION GetData (@Token varchar(50)) '+
'RETURNS varchar(max) AS '+
'BEGIN '+
'RETURN( SELECT Value FROM TestLog WHERE token = @Token ) '+
'END '
execute (@S);
exec LogData 'SourceDatabase', @SourceDatabase;
exec LogData 'DestDatabase', @DestDatabase;
exec LogData 'TempDatabase', 'ConvTemp';
SET @S = GetDate();
exec LogData 'Imported Started', @S;
-- ================= ===========
-- ================= Create the Stored Procedures ===========
-- ================= ===========
--------------------------------------------------------------------------------------
-- Section: Restore_Backup Procedure
--
-- This section defines a stored procedure that restores a backup of a database
--
--------------------------------------------------------------------------------------
CREATE PROCEDURE Restore_Backup
@DatabaseName varchar(50),
@BackupFilePath varchar(255),
@DatabaseFilePath varchar(255),
@DeleteIfExists bit = 0
AS
BEGIN
DECLARE @S varchar(max);
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = @DatabaseName )
BEGIN
IF ( @DeleteIfExists=1 )
BEGIN
SET @S =
'ALTER DATABASE '+@DatabaseName+' SET single_user WITH rollback immediate; '+
'DROP DATABASE '+@DatabaseName + '; ';
Execute(@S);
END
ELSE
RETURN; -- Nothing to do
END;
SET @S =
'restore DATABASE '+@DatabaseName + ' ' +
'FROM DISK = '''+@BackupFilePath + ''' ' +
'WITH '+
'Recovery, '+
'Move ''EmptyDataSet_Data'' TO '''+@DatabaseFilePath+@DatabaseName+'.MDF'', ' +
'Move ''EmptyDataSet_Log'' TO '''+@DatabaseFilePath+@DatabaseName+'.LDF'' ';
EXECUTE (@S);
END;
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- Section: Create_IDMapTable
--
--------------------------------------------------------------------------------------
CREATE PROCEDURE Create_IDMapTable
AS
BEGIN
USE ConvTemp
;
IF OBJECT_ID('IDMap') IS NOT NULL
DROP TABLE IDMap
;
CREATE TABLE IDMap
(
TableName varchar(50) NULL,
OldID int NOT NULL,
ClassTypeID int NOT NULL,
NewID int NULL,
IsNew bit NOT NULL,
SplitTable varchar(50) NULL,
RowIndex int IDENTITY(1,1) NOT NULL
)
;
CREATE INDEX IDMap_OldIDIndex ON IDMap (OldID ASC)
;
CREATE INDEX IDMap_NewIDIndex ON IDMap (NewID ASC)
;
CREATE INDEX IDMap_RowIndex ON IDMap (RowIndex ASC)
;
END
--------------------------------------------------------------------------------------
-- Section: Copy_Table Stored Procedure
--
-- This section defines a stored procedure that copies a table
-- from the Source database to the working table.
--
-- Other Notes:
-- * ModifiedBy fields are NULLed.
-- * ModifiedDate is set to the current date and time.
--------------------------------------------------------------------------------------
CREATE PROCEDURE Copy_Table
@SourceDatabasePath varchar(255),
@SourceTableName varchar(50),
@DestDatabasePath varchar(255),
@DestTableName varchar(50),
@CustomWhereClause varchar(max) = ''
AS
BEGIN
DECLARE @S varchar(max);
SET @SourceDatabasePath = @SourceDatabasePath+'.dbo.';
SET @DestDatabasePath = @DestDatabasePath+'.dbo.';
IF OBJECT_ID(@DestDatabasePath+@DestTableName) IS NOT NULL
Execute('DROP TABLE '+@DestDatabasePath+@DestTableName);
-- Copy the Table
SET @S =
'SELECT * INTO '+ @DestDatabasePath+@DestTableName +' ' +
'FROM '+@SourceDatabasePath+@SourceTableName+' WHERE ID > 0 ';
IF (Coalesce(@CustomWhereClause, '') '')
SET @S = @S + ' AND ('+@CustomWhereClause+')'
SET @S = @S +
'; '+
'UPDATE '+ @DestDatabasePath+@DestTableName+' '+
'SET StoreID = -1, ModifiedByUser = NULL, ModifiedByComputer = NULL, '+
'ModifiedDate = GetDate(), SeqID = 0 ' +
'; '+
'CREATE INDEX '+@TableName+'_IDIndex ON '+@DestDatabasePath+@DestTableName+' (ID ASC) '+
'; '
Execute(@S);
END
--------------------------------------------------------------------------------------
-- Section: Fill_IDMap Stored Procedure
--
-- This section fills the IDMap with values that need to be remapped
--------------------------------------------------------------------------------------
CREATE PROCEDURE Fill_IDMap
@TableName varchar(50),
@SourceDatabasePath varchar(255),
@TargetDatabasePath varchar(255),
@CustomWhereClause varchar(max) = ''
AS
BEGIN
DECLARE @TempExpr varchar(max);
DECLARE @S varchar(max);
DECLARE @StartingID int;
SET @SourceDatabasePath = @SourceDatabasePath+'.dbo.';
SET @TargetDatabasePath = @TargetDatabasePath+'.dbo.';
USE ConvTemp
;
exec Copy_Table(@SourceDatabasePath, @TableName, 'ConvTemp', @TableName, @CustomWhereClause);
-- Insert the Source values into the new Table
--
SET @S =
'INSERT INTO IDMap '+
'SELECT DISTINCT ('''+@TableName+''') as TableName, ID as OldID, '+
' ClassTypeID, NULL as NewID, NULL as SplitTableName '+
'FROM '+@TableName+' '+
'WHERE ID > 0 '
Execute(@S);
-- If any IDs already exist in the Destination Table, keep those
--
SET @S =
'UPDATE IDMap '+
'SET NewID = OldID '+
'WHERE OldID IN (SELECT ID FROM '+@TargetDatabasePath+@TableName+' Dest '+
'WHERE IDMap.ClassTypeID = Dest.ClassTypeID) ';
Execute(@S);
-- Get the Starting ID for this Table
--
SET @S =
'SET @StartingID = 1 + SELECT coalesce(Max(ID), 0) '+
'FROM '+@TargetDatabasePath+@TableName
;
Execute(@S)
;
-- Update the Map with the ID of the Destination Table for the remaining NewIDs
--
UPDATE IDMap
SET NewID = @StartingID + (SELECT COUNT(1) FROM IDMap I2
WHERE I2.TableName = @TableName AND I2.ID < IDMap.ID)
WHERE (TableName = @TableName) AND (NewID IS NULL)
END
--------------------------------------------------------------------------------------
-- Section: Remap_Field Stored Procedure
--
-- This section defines a stored procedure that remaps the values in a table based
-- on the values stored in the IDMap table.
--
-- Other Notes:
-- * An optional WhereClause can be specified for the table to control the replacement
--------------------------------------------------------------------------------------
CREATE PROCEDURE Remap_Field
@TableName varchar(50), -- = 'CustomerGoodsItem'
@FieldName varchar(255), -- = 'CategoryID'
@LookupTableName varchar(50), -- = 'PricingElement'
@CustomWhereClause varchar(max) = ''
AS
BEGIN
USE ConvTemp
;
-- Update the Field
DECLARE @S varchar(2048);
SET @S =
' UPDATE TheTable
SET '+@FieldName+' = coalesce(IDMap.NewID, '+@FieldName+')
FROM '+@TableName+' AS TheTable
LEFT JOIN IDMap
ON TheTable.'+@FieldName+' = IDMap.OldID
WHERE IDMap.TableName = '+@LookupTableName+'
AND TheTable.'+@FieldName+' IS NOT NULL
'
IF (Coalesce(@CustomWhereClause, '') '')
SET @S = @S + ' AND ('+@CustomWhereClause+')'
Execute(@S);
END
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- Section: Insert_Into_Table Stored Procedure
--
-- This section defines a stored procedure that inserts the NEW data in working table
-- into the destination database.
--
-- Other Notes:
-- * Only new data is inserted.
-- * A custom field list is generated for the working and destination table so that
-- the order of the fields does NOT need to match.
--------------------------------------------------------------------------------------
CREATE PROCEDURE Insert_Into_Table
@SourceDatabase varchar(255),
@SourceTableName varchar(50),
@DestDatabase varchar(255),
@DestTableName varchar(50),
@CustomWhereClause varchar(max) = ''
AS
BEGIN
SET @DestDatabase = @DestDatabase+'.dbo.';
SET @SourceDatabase = @SourceDatabase+'.dbo.';
DECLARE @S VarChar(max);
-- Create a list of columns common in both the source and destination tables
-- Sorted in the order found in the destination table
Declare @ColumnNames varchar(max);
SET @ColumnNames = NULL
BEGIN TRY
CREATE TABLE #ColumnNames (ColumnName Varchar(255), ColumnIndex int);
SET @S =
'INSERT INTO #ColumnNames '+
'Select * FROM '+
'( '+
' SELECT A.[name] AS ColumnName, ' +
' A.[colid] as ColumnIndex ' +
' FROM '+@DestDatabase+'syscolumns A ' +
' join '+@SourceDatabase+'syscolumns B ' +
' ON A.[name] = B.[name] ' +
' WHERE ' +
' A.id in ' +
' (SELECT id FROM '+@DestDatabase+'sysobjects ' +
' WHERE type = ''U'' ' +
' AND [NAME] = '''+@DestTableName+''') ' +
' and B.id in ' +
' (SELECT id FROM '+@SourceDatabase+'sysobjects ' +
' WHERE type = ''U'' ' +
' AND [NAME] = '''+@SourceTableName+''') ' +
') Temp ' +
'ORDER BY ColumnIndex ';
Execute(@S);
SELECT @ColumnNames = COALESCE(@ColumnNames + ',', '') + '[' + ColumnName + '] '
FROM #ColumnNames
ORDER BY ColumnIndex
-- Insert the Table Contents
SET @S=
'INSERT INTO ' + @DestDatabase+@DestTableName + ' ('+@ColumnNames+' ) ' +
'SELECT '+@ColumnNames+' FROM '+ @SourceDatabase+@SourceTableName + ' ' +
'WHERE ID > 0 AND ID NOT IN (SELECT ID FROM '+@DestDatabase+@DestTableName+') ';
IF (Coalesce(@CustomWhereClause, '') '')
SET @S = @S + ' AND ('+@CustomWhereClause+')'
-- When there is text larger than 8K, SQL will terminate with an error
-- This disabled that (not desired, but the alternate is less desireable)
--
SET ANSI_WARNINGS OFF
Print @S
Print ''
Print 'Inserting Records into '+@DestDatabase+@DestTableName
EXECUTE(@S);
SET ANSI_WARNINGS ON
-- Clean up after ourselves
DROP TABLE #ColumnNames;
END TRY
BEGIN CATCH
PRINT '-------------------------';
PRINT 'Oops! An Error Occurred.';
PRINT
' Message: '+ Coalesce(ERROR_MESSAGE(),'NULL');
PRINT
' Error Number: '+ Coalesce(Cast(ERROR_NUMBER() AS VarChar(20)),'NULL') +
' Severity: '+ Coalesce(Cast(ERROR_SEVERITY() AS VarChar(20)),'NULL') +
' State: '+ Coalesce(Cast(ERROR_STATE() AS VarChar(20)),'NULL') +
' Procedure: '+ Coalesce(Cast(ERROR_PROCEDURE() AS VarChar(50)),'NULL') +
' Line: '+ Coalesce(Cast(ERROR_LINE() AS VarChar(20)),'NULL');
PRINT ' Column Names: '+coalesce(@ColumnNames, 'NULL');
PRINT ' Last SQL Run: '+coalesce(@S, 'NULL');
PRINT '-------------------------';
END CATCH
END;
--------------------------------------------------------------------------------------
-- ================= ===========
-- ================= Restore the Backups ===========
-- ================= ===========
-- Restore the Destination Database (used for testing)
IF (@RestoreDestDatabase=1)
exec Restore_Backup @DestDatabase, @RestoreDestBackupFile, @RestoreDestFilePath, 1
-- Restore the Source Database (used for testing)
IF (@RestoreSourceDatabase=1)
exec Restore_Backup @SourceDatabase, @RestoreSourceBackupFile, @RestoreSourceFilePath, 1
-- ================= ===========
-- ================= Create the Map Table ===========
-- ================= ===========
exec Create_IDMapTable;
-- ================= ===========
-- ================= Build the Map for each Table ===========
-- ================= ===========
-- exec Fill_IDMap '', @SourceDatabase, @DestDatabase, ''
exec Fill_IDMap 'AdvQuery' , @SourceDatabase, @DestDatabase, ''
exec Fill_IDMap 'RuleMacro' , @SourceDatabase, @DestDatabase, ''
exec Fill_IDMap 'RuleAction' , @SourceDatabase, @DestDatabase, ''
exec Fill_IDMap 'Journal' , @SourceDatabase, @DestDatabase, 'ID in (Select ID from RuleAction where ID > 0 union Select ChildItemID as ID from RuleAction where ID > 0 AND ChildItemClassTypeID IN (21150,20061,21350))'
exec Fill_IDMap 'ReportMenuItem', @SourceDatabase, @DestDatabase, 'ID in (Select ChildItemID from RuleAction where ChildItemClassTypeID IN (17240,17250,17270,17290))'
-- ================= ===========
-- ================= ReMap each Field in each Table ===========
-- ================= ===========
-- exec Remap_Field '', '', '', ''
exec Remap_Field 'RuleMacro' , 'ID' , 'RuleMacro'
exec Remap_Field 'AdvQuery' , 'ID' , 'AdvQuery'
exec Remap_Field 'RuleAction' , 'ID' , 'Journal'
exec Remap_Field 'Journal' , 'ID' , 'Journal'
exec Remap_Field 'ReportMenuItem' , 'ID' , 'ReportMenuItem'
exec Remap_Field 'RuleMacro' , 'QueryID' , 'AdvQuery'
exec Remap_Field 'RuleAction', 'ParentID', 'RuleMacro'
-- -- The RuleAction.ChildItemID maps to the Journal when the ClassTypeID is not 23130
-- -- Otherwise, it maps to the ReportMenuItem table.
exec Remap_Field 'RuleAction', 'ChildItemID', 'Journal' , 'TheTable.ChildItemClassTypeID IN (21150,20061,21350)'
exec Remap_Field 'RuleAction', 'ChildItemID', 'ReportMenuItem', 'TheTable.ChildItemClassTypeID IN (17240,17250,17270,17290)'
-- ================= ===========
-- ================= Insert Data into Destination ===========
-- ================= ===========
-- exec Insert_Into_Table 'ConvTemp', '', @DestDatabase, '', ''
exec Insert_Into_Table 'ConvTemp', 'AdvQuery' , @DestDatabase, 'AdvQuery' , ''
exec Insert_Into_Table 'ConvTemp', 'RuleMacro' , @DestDatabase, 'RuleMacro' , ''
exec Insert_Into_Table 'ConvTemp', 'RuleAction' , @DestDatabase, 'RuleAction' , ''
exec Insert_Into_Table 'ConvTemp', 'Journal' , @DestDatabase, 'Journal' , ''
exec Insert_Into_Table 'ConvTemp', 'ReportMenuItem', @DestDatabase, 'ReportMenuItem', ''
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--
-- Congratulations! You are done.!
--
--------------------------------------------------------------------------------------