cqTkH7jM

1create type ReplaceTable as table(new varchar(10), old varchar(10))
2go
3
4create function dbo.tempFunc(@replacementTable ReplaceTable READONLY , @str varchar(1024))
5  returns varchar(1024)
6  as
7begin
8  declare @newString varchar(1024)
9  set @newString = @str
10  select @newString = Replace(@newString, old, new) FROM @replacementTable
11  return (@newString)
12end
13go
14
15declare @targetRegion int = 65;
16declare @maxProblemId int = (select max(Id) from Problem) + 1;
17declare @maxFieldSetId int = (select max(Id) from FieldSet) + 1;
18
19select concat(':', Id, ',') old, concat(':', Id + @maxFieldSetId, ',' ) new, FieldName, FieldType, sysdatetime() Created, Length, sysdatetime() Modified, DisplayName, IsPartOfField, RelatedField, Module, IsStatic, @targetRegion fkRegion
20  into #fieldTemp
21  from FieldSet
22
23SELECT
24   p.Id + @maxProblemId Id, p.ProblemName, p.IsActive ProblemIsActive, sysdatetime() ProblemModified, p.SortOrder ProblemSortOrder, @targetRegion fkRegion,
25   i.fkProblem, i.IssueName, i.FieldsJson, i.ActionsJson, i.IsActive IssueIsActive, i.IsNotify, i.GateVend, i.RuleDescription,
26    sysdatetime() IssuseModified, i.ShowInPage, i.SortOrder IssueSortOrder
27into #tempTable from Problem p
28left join Issue i on p.Id = i.fkProblem
29
30insert into FieldSet (FieldName, FieldType, Created, Length, Modified, DisplayName, IsPartOfField, RelatedField, Module, IsStatic, fkRegion)
31 select FieldName, FieldType, Created, Length, Modified, DisplayName, IsPartOfField, RelatedField + @maxFieldSetId, Module, IsStatic, fkRegion from #fieldTemp
32
33declare @replacementTemp ReplaceTable
34insert into @replacementTemp
35select new, old from #fieldTemp
36
37update #tempTable set FieldsJson = dbo.tempFunc(@replacementTemp, FieldsJson)
38
39SET IDENTITY_INSERT dbo.Problem ON
40
41insert into Problem (Id, ProblemName, IsActive, Modified, SortOrder, fkRegion)
42  select Id, ProblemName, ProblemIsActive IsActive, ProblemModified Modified, ProblemSortOrder SortOrder, fkRegion
43  from #tempTable
44  group by Id, ProblemName, ProblemIsActive, ProblemModified, ProblemSortOrder, fkRegion
45
46SET IDENTITY_INSERT dbo.Problem OFF
47
48insert into Issue
49  select Id fkProblem, IssueName, FieldsJson, ActionsJson, IssueIsActive IsActive, IsNotify, GateVend, RuleDescription, IssuseModified modified, ShowInPage, IssueSortOrder SortOrder
50  from #tempTable
51  where fkProblem is not null
52
53drop table if exists #fieldTemp
54drop table if exists #tempTable
55drop function if exists dbo.tempFunc
56drop type if exists ReplaceTable