· 8 years ago · Mar 19, 2018, 08:52 PM
1[2018-03-19 16:26:59] Connected
2sql> use unicas_config
3[2018-03-19 16:26:59] completed in 79ms
4sql> BEGIN
5 IF NOT EXISTS
6 (
7 SELECT * FROM [information_schema].[tables]
8 WHERE table_name = 'OnBoardingCopyProgramHistory'
9 AND table_schema = 'dbo'
10 )
11 BEGIN
12 CREATE TABLE OnBoardingCopyProgramHistory (
13 Id int identity
14 constraint PK_OnBoardingCopyProgramHistory
15 primary key,
16 [AssociationOrgId] [INT] NULL,
17 [SourceId] [INT] NULL,
18 [TargetId] [INT] NULL,
19 [TableName] [VARCHAR](50) NULL,
20 [Type] [VARCHAR](50) NULL,
21 CreatedDate DATETIME,
22 UpdatedDate DATETIME
23 )
24 END
25
26END
27
28--create temporary table
29IF object_id('#CopyProgramHistory') is null
30 CREATE TABLE #CopyProgramHistory (
31 [AssociationOrgId] [INT] NULL,
32 [SourceId] [INT] NULL,
33 [TargetId] [INT] NULL,
34 [TableName] [VARCHAR](50) NULL,
35 [Type] [VARCHAR](50) NULL,
36 CreatedDate DATETIME,
37 UpdatedDate DATETIME)
38
39
40--DECLARE @IDs TABLE (OrgId INT, SourceId INT, TargetId INT, TableName VARCHAR(50), Type VARCHAR(50))
41DECLARE @IDs TABLE (AssociationOrgId INT, SourceId INT, TargetId INT, TableName VARCHAR(50), Type VARCHAR(50))
42--Program to be copied
43DECLARE @programID int
44SET @programID = 123387
45
46SET XACT_ABORT ON
47SET NOCOUNT ON
48
49BEGIN TRANSACTION
50
51
52--Gather all Association Organizations to which the program needs to be copied
53MERGE @IDs AS T
54USING (SELECT Id, OrganizationId, FormId
55 FROM dbo.AssociationOrg
56 --excluding the AssociationOrg which already has the program
57 WHERE FormId IN (5252) AND Id != (SELECT AssociationOrgId FROM dbo.Program WHERE Program.Id = @programID)
58 ) AS S
59ON 0 = 1
60WHEN NOT MATCHED THEN
61 INSERT (AssociationOrgId, SourceId, TargetId, TableName, Type )
62 VALUES (S.Id, @programID, @programID, 'AssociationOrg', S.FormId );
63
64INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
65 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'AssociationOrg'
66
67
68
69--Copy Program
70MERGE dbo.Program AS T
71USING (SELECT Id, ProgramCode, ProgramName, ProgramLevel, Department, Track, AcademicYear, StartTerm, Delivery, Fee, City, State, StartDate, Deadline,
72 DeadlineDisplay, ProgramType, I.AssociationOrgId AS AssociationOrgId, ApplicationType, Concentration, CreatedDate, UpdatedDate, ZipCode, Campus, WADisplayName, Status, EarlyDecisionEnable
73 FROM dbo.Program p
74 INNER JOIN @IDs I ON p.Id = I.TargetId AND I.TableName = 'AssociationOrg'
75 ) AS S
76ON 0 = 1
77WHEN NOT MATCHED THEN
78 INSERT (ProgramCode, ProgramName, ProgramLevel, Department, Track, AcademicYear, StartTerm, Delivery, Fee, City, State, StartDate, Deadline,
79 DeadlineDisplay, ProgramType, AssociationOrgId, ApplicationType, Concentration, CreatedDate, UpdatedDate, ZipCode, Campus, WADisplayName, Status, EarlyDecisionEnable)
80 VALUES (S.ProgramCode, S.ProgramName, S.ProgramLevel, S.Department, S.Track, S.AcademicYear, S.StartTerm, S.Delivery, S.Fee, S.City, S.State, S.StartDate, S.Deadline,
81 S.DeadlineDisplay, S.ProgramType, S.AssociationOrgId, S.ApplicationType, S.Concentration, S.CreatedDate, S.UpdatedDate, S.ZipCode, S.Campus, S.WADisplayName, S.Status, S.EarlyDecisionEnable)
82OUTPUT S.AssociationOrgId,S.Id,inserted.Id, 'Program', NULL INTO @IDs;
83
84
85INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
86 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'Program'
87
88
89--Copy Program Branding Configs
90MERGE dbo.ProgramBrandingConfig AS T
91USING (SELECT p.Id, p.configName, p.configValue, I.TargetId AS programId, CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate, I.AssociationOrgId
92 FROM dbo.ProgramBrandingConfig p
93 INNER JOIN @IDs I ON p.programId = I.SourceId AND I.TableName = 'Program'
94 ) AS S
95ON 0 = 1
96WHEN NOT MATCHED THEN
97 INSERT (configName, configValue, programId, CreatedDate, UpdatedDate)
98 VALUES (S.configName, S.configValue, S.programId, S.CreatedDate, S.UpdatedDate)
99OUTPUT S.AssociationOrgId, S.Id, inserted.Id, 'ProgramBrandingConfig', NULL INTO @IDs;
100
101INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
102 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'ProgramBrandingConfig'
103
104
105--Copy Supplemental Sections
106MERGE dbo.SupplementalSection AS T
107USING (SELECT s.Id, s.SectionName, CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate, I.TargetId AS ProgramId, s.Instructions,
108 s.IsChildPresent, s.FormSubSectionId, s.DisplayName, s.SortIdx,s.AssociationOrgId, I.AssociationOrgId AS OrgId
109 FROM dbo.SupplementalSection s
110 INNER JOIN @IDs AS I ON s.ProgramId = I.SourceId AND I.TableName = 'Program'
111 ) AS S
112ON 0 =1
113WHEN NOT MATCHED THEN
114 INSERT (SectionName, CreatedDate, UpdatedDate, ProgramId, Instructions, IsChildPresent, FormSubSectionId, DisplayName,
115 SortIdx, AssociationOrgId)
116 VALUES (S.SectionName, S.CreatedDate, S.UpdatedDate, S.ProgramId, S.Instructions, S.IsChildPresent, S.FormSubSectionId, S.DisplayName,
117 S.SortIdx, S.AssociationOrgId)
118OUTPUT S.OrgId,S.Id, inserted.Id,'SupplementalSection', NULL INTO @IDs;
119
120
121INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
122 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'SupplementalSection'
123
124
125
126--Copy Question Blocks
127MERGE dbo.QuestionBlock AS T
128USING (SELECT q.Id, q.Title, q.Instructions, q.IsEnabled, q.SequenceNumber, I.TargetId AS SectionId, CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate,
129 q.ProfileId, q.StandardName, q.Locked, I.AssociationOrgId
130 FROM dbo.QuestionBlock q
131 INNER JOIN @IDs AS I ON q.SectionId = I.SourceId AND I.TableName = 'SupplementalSection'
132 ) AS S
133ON 0 = 1
134WHEN NOT MATCHED THEN
135 INSERT (Title, Instructions, IsEnabled, SequenceNumber, SectionId, CreatedDate, UpdatedDate, ProfileId, StandardName, Locked)
136 VALUES (S.Title, S.Instructions, S.IsEnabled, S.SequenceNumber, S.SectionId, S.CreatedDate, S.UpdatedDate, S.ProfileId, S.StandardName, S.Locked)
137OUTPUT S.AssociationOrgId,S.Id, inserted.Id, 'QuestionBlock', 'block' INTO @IDs;
138
139INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
140 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'QuestionBlock'
141
142
143
144--Copy Questions
145MERGE dbo.Question AS T
146USING (SELECT q.Id, q.QuestionTypeId, q.QuestionText, q.SequenceNumber, q.IsRequired, CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate,
147 I.TargetId AS QuestionBlockId, q.ResponseTypeId, q.DisplayTypeId,q.Hidden, I.AssociationOrgId
148 FROM dbo.Question q
149 INNER JOIN @IDs AS I ON q.QuestionBlockId = I.SourceId AND I.TableName = 'QuestionBlock'
150 ) AS S
151ON 0 = 1
152WHEN NOT MATCHED THEN
153 INSERT (QuestionTypeId, QuestionText,SequenceNumber, IsRequired, CreatedDate, UpdatedDate, QuestionBlockId, ResponseTypeId, DisplayTypeId, Hidden)
154 VALUES (S.QuestionTypeId, S.QuestionText,S.SequenceNumber, S.IsRequired, S.CreatedDate, S.UpdatedDate, S.QuestionBlockId, S.ResponseTypeId, S.DisplayTypeId, S.Hidden)
155OUTPUT S.AssociationOrgId, S.Id, inserted.Id, 'Question', 'question' INTO @IDs;
156
157
158INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
159 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'Question'
160
161
162
163--Copy Question Details
164MERGE dbo.QuestionDetails AS T
165USING (SELECT q.Id, q.AliasText, q.AnswerOption, q.IsEnabled, q.IsRequired, q.MaxChars, q.SequenceNum, I.TargetId AS QuestionId,
166 CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate, q.ShowComma, q.AllowedDecimal, q.IsVertical, I.AssociationOrgId
167 FROM dbo.QuestionDetails q
168 INNER JOIN @IDs AS I ON q.QuestionId = I.SourceId AND I.TableName = 'Question'
169 ) AS S
170ON 0 = 1
171WHEN NOT MATCHED THEN
172 INSERT (AliasText, AnswerOption,IsEnabled, IsRequired, MaxChars, SequenceNum, QuestionId, CreatedDate, UpdatedDate, ShowComma, AllowedDecimal, IsVertical)
173 VALUES (S.AliasText, S.AnswerOption,S.IsEnabled, S.IsRequired, S.MaxChars, S.SequenceNum, S.QuestionId, S.CreatedDate, S.UpdatedDate, S.ShowComma, S.AllowedDecimal, S.IsVertical)
174OUTPUT S.AssociationOrgId,S.Id, inserted.Id, 'QuestionDetails', NULL INTO @IDs;
175
176
177INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
178 SELECT AssociationOrgId, SourceId, TargetId, TableName, NULL, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'QuestionDetails'
179
180
181--Copy Conditional Questions
182MERGE dbo.QuestionDetailsConditionalRules AS T
183USING (SELECT q.id, J.TargetId AS QuestionDetailId, I.TargetId AS ConditionalQuestionId, q.conditionalType, q.conditionalOperator,
184 CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate, q.PairedQuestion, I.AssociationOrgId
185 FROM dbo.QuestionDetailsConditionalRules q
186 INNER JOIN @IDs AS I ON q.conditionalQuestionId = I.SourceId AND q.conditionalType = I.Type
187 INNER JOIN @IDs AS J ON q.questionDetailId = J.SourceId AND J.TableName = 'QuestionDetails'
188 AND I.AssociationOrgId = J.AssociationOrgId
189 ) AS S
190ON 0 =1
191WHEN NOT MATCHED THEN
192 INSERT (questionDetailId, conditionalQuestionId, conditionalType, conditionalOperator, CreatedDate, UpdatedDate, PairedQuestion)
193 VALUES (S.questionDetailId, S.conditionalQuestionId, S.conditionalType, S.conditionalOperator, S.CreatedDate, S.UpdatedDate, S.PairedQuestion)
194OUTPUT S.AssociationOrgId, S.Id, inserted.id, 'QuestionDetailsConditionalRules', NULL INTO @IDs;
195
196INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
197 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'QuestionDetailsConditionalRules'
198
199
200-- Copy Paired Questions
201MERGE dbo.QuestionDetailsPairedQuestions AS T
202USING (SELECT q.Id, I.TargetId AS QuestionDetailId, J.TargetId AS DependentQuestionDetailId,
203 CURRENT_TIMESTAMP AS CreatedDate, CURRENT_TIMESTAMP AS UpdatedDate, I.AssociationOrgId
204 FROM dbo.QuestionDetailsPairedQuestions q
205 INNER JOIN @IDs AS I ON q.QuestionDetailId = I.SourceId AND I.TableName = 'QuestionDetails'
206 INNER JOIN @IDs AS J ON q.DependentQuestionDetailId = J.SourceId AND J.TableName = 'QuestionDetails'
207 AND I.AssociationOrgId = J.AssociationOrgId
208 ) AS S
209ON 0 =1
210WHEN NOT MATCHED THEN
211 INSERT (QuestionDetailId, DependentQuestionDetailId, CreatedDate, UpdatedDate)
212 VALUES (S.QuestionDetailId, S.DependentQuestionDetailId, S.CreatedDate, S.UpdatedDate)
213OUTPUT S.AssociationOrgId, S.Id, inserted.Id, 'QuestionDetailsPairedQuestions', NULL INTO @IDs;
214
215
216INSERT #CopyProgramHistory (AssociationOrgId, SourceId, TargetId, TableName, Type, CreatedDate, UpdatedDate)
217 SELECT AssociationOrgId, SourceId, TargetId, TableName, Type, GETDATE(), GETDATE() FROM @IDs WHERE TableName = 'QuestionDetailsPairedQuestions'
218
219
220--Save the temporary table info for record
221INSERT OnBoardingCopyProgramHistory
222 SELECT * FROM #CopyProgramHistory
223DROP TABLE #CopyProgramHistory
224
225
226COMMIT TRANSACTION
227
228SET NOCOUNT OFF
229[2018-03-19 16:27:04] This property is not supported: ApplicationName.
230[2018-03-19 16:27:04] completed in 4s 983ms