' IsNew 1 '+
'WHERE TargetID IS NULL '
IF (@DEBUG 1) Print @S;
Execute(@S);
END;
SET @S
'UPDATE '+@MapTableName+' '+
'SET IsChanged 1 '+
'WHERE (IsNew 1) OR (TargetID SourceID) ';
IF (@DEBUG 1) Print @S;
Execute(@S);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'AS BEGIN
- - SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
- - Update the Field
DECLARE @S varchar(2048);
SET @S' 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'
IF (@WhereClause IS NOT NULL and @WhereClause '')
BEGIN
SET @S @S + ' AND '+REPLACE( @WhereClause, 'TABLE.', 'TheTable.' );
- - Print @S;
END;
Execute(@S)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 OUTPUTAS BEGIN
SET @ID -1;
SET @ClassTypeID -1;
IF (@IDString IS NULL)
RETURN;
DECLARE @S varchar(max);
DECLARE @StartPos int;
DECLARE @EndPos int;
DECLARE @LastIDFound bit;
DECLARE @TempNewIDs Table(TextValue varchar(255), SourceID int, TargetID int);
- - Retrieve the ID Field
SET @StartPos CharIndex( '(', @IDString );
SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 );
IF (@StartPos < 1) or (@EndPos < 1)
RETURN;
SET @ID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int );
IF @ID < 0
RETURN;
SET @S' SELECT TextValue, SourceID, TargetID
FROM IDMap_'+@MapTable+'
WHERE SourceID '+Cast(@ID as VarChar(12))+'';
INSERT INTO @TempNewIDs Exec(@S)
- - There should only be one record
SET @ID (SELECT Top 1 TargetID from @TempNewIDs)
IF (@ID IS NULL)
RETURN;
- - Retrieve the Next ID Field
SET @LastIDFound 0;
SET @StartPos @EndPos;
SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 );
IF @EndPos 0
BEGIN
SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 );
SET @LastIDFound 1;
END;
IF (@StartPos < 1) or (@EndPos < 1)
RETURN;
IF (@LastIDFound 1)
BEGIN
SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int );
RETURN;
END; -- ELSE Skip it
- - Retrieve the Next ID Field
SET @StartPos @EndPos;
SET @EndPos CharIndex( ')', @IDString, @StartPos + 1 );
IF @EndPos 0 SET @EndPos CharIndex( ',', @IDString, @StartPos + 1 );
IF @EndPos 0 SET @EndPos CharIndex( ' ', @IDString, @StartPos + 1 );
IF (@StartPos < 1) or (@EndPos < 1)
RETURN;
SET @ClassTypeID cast( substring(@IDString, @StartPos+1, (@EndPos-@StartPos)-1) as int );
RETURN;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';AS BEGIN
DECLARE @S varchar(max);
SET NOCOUNT ON;
- - 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+''' )
WHERE ID '+cast(@CurrentRowID as varchar(12))+'';
EXECUTE( @S );
FETCH TempCursor INTO @CurrentRowID, @CurrentIDRecordStr;
END;
DROP TABLE #TempTable;END
GO
– Function to parse a UDF field name into a valid Database Field Name CREATE FUNCTION MakeFieldNameFriendly
(
@inputstr VARCHAR(255)
)RETURNS VARCHAR(255) AS BEGIN
DECLARE @stripchrs varchar(15);
DECLARE @charcounter INT;
SET @stripchrs ' ./+()-%?';
SET @charcounter 1;
WHILE @charcounter