This document describes the process (and SQLs) used to merge one or more Control products from one database to another.

WARNING: This article is for reference. The changes made will PERMANENTLY delete and/or affect your data. This is the exact procedure in almost NO cases and 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:

  1. Create a map table (containing OLDID, NEWID) for each table being moved
  2. Extract the data to be copied into Temp Tables
  3. ReIndex the database fields
  4. ReIndex the XML fields
  5. Insert the temp tables

If there are any conflicts, the existing data will always take precedence.

Guidelines

Items not imported:

Other Notes:

Tables Addressed

The following Database Tables are involved in pricing in Control. This grid outlines which are converted into the new system:

~ Table Name~ Converted
* sql_structure_-_catalogitem_tableNo
* sql_structure_-_customergoodsitem_tableYes
* sql_structure_-_customrange_tableNo
* sql_structure_-_discounttable_tableYes
* sql_structure_-_discounttableitem_tableYes
* sql_structure_-_element_tableYesSelect rows from the Element Table that deal with pricing are brought over.
* sql_structure_-_glaccount_tableNo
* sql_structure_-_goodsitempartlink_tableYes
* sql_structure_-_inventory_table??
* sql_structure_-_inventorylog_table??
* sql_structure_-_iotemplate_tableYes
* sql_structure_-_marketinglistitem_tableYes
* sql_structure_-_part_tableYes
* sql_structure_-_partinfotemplate_tableYes
* sql_structure_-_partinventoryconversion_tableYes
* sql_structure_-_partuserfield_tableYes Only UDF values for columns already defined will be imported.
* sql_structure_-_pricingelement_tableYes
* sql_structure_-_pricinggraphic_tableYes
* sql_structure_-_pricinglevel_tableYes
* sql_structure_-_pricinglink_tableYes
* sql_structure_-_pricingplan_tableYes
* sql_structure_-_pricingtable_tableYes
* sql_structure_-_prodmodlink_tableYes
* sql_structure_-_producttaxabilitycode_tableYes
* SQL Structure - ProductTaxExemptLink TableYes
* sql_structure_-_productuserfield_tableYes
* sql_structure_-_quickproduct_tableNo
* sql_structure_-_selectionlist_tableYesBecause they are stored in an XML record structure, Selection List links to parts and dependent selection lists are not converted.
* sql_structure_-_selectionlistitem_tableYesBecause they are stored in an XML record structure, Selection List links to parts and dependent selection lists are not converted.
* sql_structure_-_statetaxexemptionlink_tableNo
* sql_structure_-_station_tableNo
* sql_structure_-_storeconstant_tableYes
* sql_structure_-_taxclass_tableNo
* sql_structure_-_taxlink_tableNo
* sql_structure_-_userfielddef_tableYes
* sql_structure_-_userfieldlayout_tableYes
* sql_structure_-_username_tableYes
* sql_structure_-_variable_tableYes

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 @RenameExistingProducts bit; DECLARE @RenameExistingParts bit; DECLARE @RenameExistingModifiers bit; DECLARE @RenameExistingSelectionLists bit; DECLARE @RenamePrefix varchar(255); DECLARE @RenameSuffix varchar(255); DECLARE @DeleteDestPricingForms bit; DECLARE @DeleteDestUDFLayouts bit; DECLARE @S varchar(max);

SET @SourceDatabase 'Scott_Latest_OOBData'; SET @DestDatabase 'Scott_Hansen_Updated';

SET @DeleteDestPricingForms 0; – Set this to 1 to delete ALL pricing forms in the destination before importing SET @DeleteDestUDFLayouts 0; – Set this to 1 to delete ALL UDF Layouts in the destination before importing SET @RenameExistingProducts 0; – Set this to 1 to rename ALL products in the destination with the prefix and suffix before importing SET @RenameExistingParts 0; – Set this to 1 to rename ALL parts in the destination with the prefix and suffix before importing SET @RenameExistingModifiers 0; – Set this to 1 to rename ALL modifiers in the destination with the prefix and suffix before importing SET @RenameExistingSelectionLists 0; – Set this to 1 to rename ALL selection lists in the destination with the prefix and suffix before importing SET @RenamePrefix 'Old_'; SET @RenameSuffix ; 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; exec LogData 'DeleteDestPricingForms' , @DeleteDestPricingForms; exec LogData 'DeleteDestUDFLayouts' , @DeleteDestUDFLayouts; exec LogData 'RenameExistingProducts' , @RenameExistingProducts; exec LogData 'RenameExistingParts' , @RenameExistingParts ; exec LogData 'RenameExistingModifiers', @RenameExistingModifiers; exec LogData 'RenameExistingSelectionLists', @RenameExistingSelectionLists; exec LogData 'RenamePrefix' , @RenamePrefix; exec LogData 'RenameSuffix' , @RenameSuffix; – Restore the Destination Database (used for testing) – IF (@RestoreDestDatabase1) BEGIN SET @S 'IF EXISTS(SELECT * FROM sys.DATABASES WHERE name '+@DestDatabase + ') ' + 'begin '+ 'ALTER DATABASE '+@DestDatabase+' SET single_user WITH rollback immediate; '+ 'DROP DATABASE '+@DestDatabase + '; ' + 'end; '+ 'restore DATABASE '+@DestDatabase + ' ' + 'FROM DISK '+@RestoreDestBackupFile + ' ' + 'WITH '+ 'Recovery, '+ 'Move EmptyDataSet_Data TO '+@RestoreDestFilePath+'StoreDataDest.MDF, ' + 'Move EmptyDataSet_Log TO '+@RestoreDestFilePath+'StoreDataDest.LDF '; EXECUTE (@S); END – Restore the Source Database (used for testing) – IF (@RestoreSourceDatabase1) BEGIN SET @S 'IF EXISTS(SELECT * FROM sys.DATABASES WHERE name '+@SourceDatabase + ') ' + 'begin '+ 'ALTER DATABASE '+@SourceDatabase+' SET single_user WITH rollback immediate; '+ 'DROP DATABASE '+@SourceDatabase + '; ' + 'end; '+ 'restore DATABASE '+@SourceDatabase + ' ' + 'FROM DISK '+@RestoreSourceBackupFile + ' ' + 'WITH '+ 'Recovery, '+ 'Move EmptyDataSet_Data TO '+@RestoreSourceFilePath+'StoreDataSource.MDF, ' + 'Move EmptyDataSet_Log TO '+@RestoreSourceFilePath+'StoreDataSource.LDF '; EXECUTE (@S); END ————————————————————————————– GO ————————————————————————————– – Section: Create_IDMap Stored Procedure – – This section defines a stored procedure that creates an IDMap table in the – in the temporary database. The IDMap table: – * Needs to be called for each table you want to create. – * Is names IDMap_xxxxxx where xxxxxx is the table name. – * Matches based on a text expression passed in – * Generates new IDs for records not found ————————————————————————————– CREATE PROCEDURE Create_IDMap @TableName varchar(50), @TextExpressionCompared varchar(max), @ClassTypeID int -1, @SourceDatabasePath varchar(255), @TargetDatabasePath varchar(255), @CreateNewIDs bit AS BEGIN DECLARE @DEBUG bit; DECLARE @MapTableName varchar(100); DECLARE @TempExpr varchar(max); DECLARE @ClassTypeIDStr varchar(10); DECLARE @S varchar(max); SET @DEBUG 0; Execute( 'USE '+@SourceDatabasePath ); SET @SourceDatabasePath @SourceDatabasePath+'.dbo.'; SET @TargetDatabasePath @TargetDatabasePath+'.dbo.'; IF @ClassTypeID -1 SET @MapTableName 'IDMap_'+@TableName ELSE SET @MapTableName 'IDMap_'+@TableName+'_'+cast(@ClassTypeID as varchar(10)); SET @ClassTypeIDStr CAST(@ClassTypeID as VarChar(10)); – SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. IF (@DEBUG 1) SET NOCOUNT ON; IF OBJECT_ID(@MapTableName) IS NOT NULL Execute('DROP TABLE '+@MapTableName); – Create a mapping table SET @S 'USE ConvTemp; '+ 'CREATE TABLE '+@MapTableName+' '+ '( '+ ' TextValue varchar(255) NULL, '+ ' SourceID int NOT NULL, '+ ' TargetID int NULL, '+ ' IsNew bit NOT NULL, '+ ' IsChanged bit NOT NULL, '+ ' RowIndex int IDENTITY(1,1) NOT NULL '+ ') '+ '; '+ 'CREATE INDEX '+@MapTableName+'_SourceIDIndex ON '+@MapTableName+' (SourceID ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_TargetIDIndex ON '+@MapTableName+' (TargetID ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_TextValueIndex ON '+@MapTableName+' (TextValue ASC) '+ '; '+ 'CREATE INDEX '+@MapTableName+'_RowIndex ON '+@MapTableName+' (RowIndex ASC) '+ '; ' IF (@DEBUG 1) Print @S; Execute(@S); – In order to support combined fields, allow an expression in the TextExpressionCompared – To handle this, we have to accomodate the “Table.” prefix anywhere in the expression. – If the just pass in a field, then we need to pre-prend the “Table.” prefix. IF (PATINDEX('%TABLE.%', @TextExpressionCompared) 0) SET @TextExpressionCompared 'TABLE.'+@TextExpressionCompared; – Insert the Source values into the new Table SET @TempExpr REPLACE( REPLACE( @TextExpressionCompared, 'TABLE.', 'SRC.' ), 'DATABASE.', +@SourceDatabasePath+); SET @S 'INSERT INTO '+@MapTableName+' '+ 'SELECT DISTINCT ('+@TempExpr+'), ID, NULL, 0, 0 '+ 'FROM '+@SourceDatabasePath+@TableName+' AS SRC '+ 'WHERE ID > 0 ' IF (@ClassTypeID -1) SET @S @S + ' AND (ClassTypeID '+ @ClassTypeIDStr +')'; IF (@DEBUG 1) Print @S; Execute(@S); – In Case of Duplicates, Delete all but the highest SET @S 'DELETE FROM '+@MapTableName+' '+ 'WHERE RowIndex NOT IN (SELECT Min(RowIndex) FROM '+@MapTableName+' GROUP BY SourceID)'; IF (@DEBUG 1) Print @S; Execute(@S); – Do not Renumber System IDs regardless of the name. SET @S ' UPDATE '+@MapTableName+' SET TargetID SourceID, IsNew (CASE WHEN SourceID IN (SELECT ID FROM '+@TargetDatabasePath+@TableName+' ) THEN 0 ELSE 1 END) WHERE SourceID < 1000 ;' IF (@DEBUG 1) Print @S; Execute(@S); – Update the Map with the ID of the Destination Table SET @TempExpr REPLACE( REPLACE( @TextExpressionCompared, 'TABLE.', 'DestTable.' ), 'DATABASE.', +@TargetDatabasePath+); SET @S 'UPDATE MapTable '+ 'SET TargetID DestTable.ID '+ 'FROM '+@MapTableName+' MapTable '+ ' LEFT JOIN '+@TargetDatabasePath+@TableName+' DestTable '+ ' ON MapTable.TextValue ('+@TempExpr+') '+ 'WHERE TargetID IS NULL '; IF (@DEBUG 1) Print @S; Execute(@S); – Presumable, if there is no map field then the item does not exist. – In this case, give it a unique ID if desired IF (@CreateNewIDs1) BEGIN SET @S 'UPDATE '+@MapTableName+' '+ 'SET TargetID RowIndex + Coalesce1)

1)
SELECT max(ID) FROM '+@TargetDatabasePath+@TableName+'), 1000), '+ ' IsNew 1 '+
          'WHERE TargetID IS NULL '
  IF (@DEBUG  1) Print @S;
  Execute(@S);
END;
_FCKG_BLANK_TD_ SET @S
          'UPDATE '+@MapTableName+' '+
          'SET IsChanged  1 '+
          'WHERE (IsNew  1) OR (TargetID  SourceID) ';
IF (@DEBUG  1) Print @S;
Execute(@S);
_FCKG_BLANK_TD_ END GO – 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'
    @MapTable varchar(50),            --  'PricingElement'
    @WhereClause varchar(max)  NULL  --  'ClassTypeID  12000'
_FCKG_BLANK_TD_ AS BEGIN @@_SP_@@ - SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; @@_SP_@@ - Update the Field DECLARE @S varchar(2048); SET @S _FCKG_BLANK_TD_ ' UPDATE TheTable SET '+@FieldName+' coalesce(MapTable.TargetID, '+@FieldName+')
         FROM Temp_'+@TableName+' AS TheTable
         LEFT JOIN IDMap_'+@MapTable+' AS MapTable
         ON TheTable.'+@FieldName+'  MapTable.SourceID
         WHERE TheTable.'+@FieldName+' IS NOT NULL
_FCKG_BLANK_TD_ IF (@WhereClause IS NOT NULL and @WhereClause ) BEGIN SET @S @S + ' AND '+REPLACE( @WhereClause, 'TABLE.', 'TheTable.' ); _FCKG_BLANK_TD_ @@_SP_@@ - Print @S; END; Execute(@S) _FCKG_BLANK_TD_ END GO – Section: Parse_IDField Stored Procedure – – This section creates a stored procedure used to Parse and input string from a format – like (6034,1000,12000) or (6034,12000). The resulting ID and ClassTypeID are passed – back as OUTPUT parameters. – – Other Notes: – * In most cases, invalid data will result in -1,-1 being passed back. CREATE Procedure Parse_IDField @IDString varchar(50), @MapTable varchar(50), @ID int OUTPUT, @ClassTypeID int OUTPUT _FCKG_BLANK_TD_ AS BEGIN SET @ID -1; SET @ClassTypeID -1; _FCKG_BLANK_TD_ IF (@IDString IS NULL) RETURN; _FCKG_BLANK_TD_ DECLARE @S varchar(max); DECLARE @StartPos int; DECLARE @EndPos int; DECLARE @LastIDFound bit; _FCKG_BLANK_TD_ DECLARE @TempNewIDs Table(TextValue varchar(255), SourceID int, TargetID int); _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the ID Field SET @StartPos CharIndex( '(', @IDString ); SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ SET @ID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); IF @ID <0 RETURN; _FCKG_BLANK_TD_ SET @S _FCKG_BLANK_TD_ ' SELECT TextValue, SourceID, TargetID FROM IDMap_'+@MapTable+' WHERE SourceID '+Cast(@ID as VarChar(12))+' _FCKG_BLANK_TD_ INSERT INTO @TempNewIDs Exec(@S) _FCKG_BLANK_TD_ @@_SP_@@ - There should only be one record SET @ID (SELECT Top 1 TargetID from @TempNewIDs) IF (@ID IS NULL) RETURN; _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the Next ID Field SET @LastIDFound 0; SET @StartPos @EndPos; SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF @EndPos 0 BEGIN SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 ); SET @LastIDFound 1; END; _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ IF (@LastIDFound 1) BEGIN SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); RETURN; END; – ELSE Skip it _FCKG_BLANK_TD_ @@_SP_@@ - Retrieve the Next ID Field SET @StartPos @EndPos; SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF @EndPos 0 SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 ); IF @EndPos 0 SET @EndPos CharIndex( ' ', @IDString, @StartPos + 1 ); _FCKG_BLANK_TD_ IF (@StartPos <1) or (@EndPos <1) RETURN; _FCKG_BLANK_TD_ SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int ); RETURN; _FCKG_BLANK_TD_ END GO – Section: Remap_XML Stored Procedure – – This section defines a stored procedure that remaps ID Fields contained within – XML data. The technique used: – * Extracts the ID Record node (as a string) – * Uses Parse_IDField to retrieve the ID – * Looks up the new ID – * Replaces the ID Record in the XML with the new ID record – – Other Notes: – * This needs to be called individually on each XML field in a table. – * The XML node path and node text must be static (invariant) CREATE PROCEDURE Remap_XMLData @TableName varchar(50), – 'SelectionListItem'; @FieldName varchar(255), – 'DependentLinksXML'; @NodePath varchar(255), – 'DependentListLink'; @NodeText varchar(255), – 'DependentListID'; @MapTable varchar(50) – 'SelectionList'; _FCKG_BLANK_TD_ AS BEGIN DECLARE @S varchar(max); SET NOCOUNT ON; _FCKG_BLANK_TD_ @@_SP_@@ - Create a temp table to use to iterate through and change the data CREATE TABLE #TempTable (ID int, IDRecordStr varchar(55)) ; CREATE INDEX TempTableIDIndex on #TempTable (ID); – Build a table of ID Records to be replaced SET @S ' SELECT SourceTable.ID, LinkRow.value( ('+@NodeText+')[1], varchar(55)) IDRecordStr FROM ( SELECT ID, Cast('+@FieldName+' AS XML) XMLField FROM Temp_'+@TableName+' WHERE ('+@FieldName+' IS NOT NULL) AND (Len(Cast('+@FieldName+' AS VarChar(55))) > 20) ) SourceTable CROSS APPLY XMLField.nodes('+@NodePath+') AS LinkTable(LinkRow) WHERE LinkRow.value( ('+@NodeText+')[1], varchar(55)) NOT IN ( (-1,-1,-1) , (-1,-1)) '; – Fill in the temp table INSERT INTO #TempTable EXECUTE (@S) DECLARE TempCursor CURSOR FOR SELECT * FROM #TempTable; DECLARE @CurrentRowID int; DECLARE @CurrentIDRecordStr varchar(55); DECLARE @ReplacementIDRecordStr varchar(55); DECLARE @CurrentID int; DECLARE @CurrentClassTypeID int; OPEN TempCursor; – Loop through each record FETCH TempCursor INTO @CurrentRowID, @CurrentIDRecordStr; WHILE @@Fetch_Status 0 BEGIN – Parse the Record String exec Parse_IDField @CurrentIDRecordStr, @MapTable, @CurrentID OUTPUT, @CurrentClassTypeID OUTPUT – Generate the Replacement String SET @ReplacementIDRecordStr '('+cast(@CurrentID as varchar(12))+','+cast(@CurrentClassTypeID as varchar(12))+')'; – Update the record SET @S ' UPDATE Temp_'+@TableName+' SET '+@FieldName+' REPLACE( cast('+@FieldName+' AS VarChar(max)), '+@CurrentIDRecordStr+', '+@ReplacementIDRecordStr+' ) <code> WHERE ID '+cast(@CurrentRowID as varchar(12))+' </code> '; <code> EXECUTE( @S ); </code> FETCH TempCursor INTO @CurrentRowID, @CurrentIDRecordStr; <code> END; </code> DROP TABLE #TempTable; END —- GO —- – Function to parse a UDF field name into a valid Database Field Name CREATE FUNCTION MakeFieldNameFriendly <code> ( @inputstr VARCHAR(255) ) </code> RETURNS VARCHAR(255) AS BEGIN <code> DECLARE @stripchrs varchar(15); DECLARE @charcounter INT; SET @stripchrs ' ./+()-%?'; SET @charcounter 1; WHILE @charcounter </code> —- <code> </code> <code> </code> —- —- —- <code> </code> - - <code> </code> <code> </code> <code> </code> - <code> </code> —- —- —- <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> - <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> <code> </code> - <code> </code> - <code> </code> <code> </code> <code> </code> <code> </code> - <code> </code> <code> </code> <code> </code> <code> </code> —- —- —- <code> </code> <code> </code> - ''