· 5 years ago · Apr 17, 2020, 02:20 PM
1USE [Dev_DDS_ODS_Stage]
2GO
3
4
5SET NOCOUNT ON
6
7/*** Declare global variables ***/
8DECLARE
9 /*** Log Value Variables ***/
10 @RowCountParam NVARCHAR(100) = N'@RowCount INT OUTPUT' --Global Row Count variable to pass into "sp_executesql" function
11 ,@ESRowCount INT --Row count of Eaglesoft table results
12 ,@ODSRowCount INT --Row count of existing ODS table results
13 ,@ComparisonRowCount INT --Count of rows missing from ODS that exist in Eaglesoft
14 ,@ESRowCountQuery NVARCHAR(300) = N'' --Query to grab row count for Eaglesoft Table
15 ,@ODSRowCountQuery NVARCHAR(300) = N'' --Query to grab row count for ODS Table
16 ,@ComparisonQuery NVARCHAR(MAX) = N'' --Query to compare Eaglesoft and ODS table
17
18
19 ,@LiveTableQuery NVARCHAR(MAX) = N''
20 ,@ODSTableQuery NVARCHAR(MAX) = N''
21 ,@LiveTableCount BIGINT = 0
22 ,@ODSTableCount BIGINT = 0
23 ,@ExceptionCount BIGINT = 0
24
25 /*** LOOPS ***/
26 ,@ServerID VARCHAR(3) = ''
27 ,@TableIDCounter INT = 1 --Table ID LOOP Iteration
28 ,@TableIDMax INT = 0 --Table ID Max to determine LOOP end
29 ,@ColumnIDMax INT = 0 --Column ID LOOP Iteration
30 ,@ColumnIDCounter INT = 0 --Column ID Max to determine LOOP end
31
32 /*** Dynamic Query Variables ***/
33 ,@TableIDRange NVARCHAR(MAX) = N'' --Range of Table IDs; Planned to be used to check if LOOP iteration exists within range
34 ,@ColumnIDRange NVARCHAR(800) = N'' --Range of Column IDs of the table; Planned to be used to check if LOOP iteration exists within range
35 ,@ODSColNameRange NVARCHAR(MAX) = N'' --Range of ODS Column Names for Dynamic Queries
36 ,@ESColNameRange NVARCHAR(MAX) = N'' --Range of ES Column Names for Dynamic Queries
37 ,@ESSelectRange NVARCHAR(MAX) = N'' --Range of ES Columns without conversions
38 ,@ODSTableName NVARCHAR(150) = N'' --ODS Schema and Table in the [dbo].[Table] format for Dynamic Queries
39 ,@ESTableName NVARCHAR(150) = N'' --ES Schema and Table in the [dbo].[Table] format for Dynamic Queries
40 ,@SubstringMax INT = 800 --For limiting large data cells from Live server (long varchar)
41 ,@ESTempTable NVARCHAR(150) = N'' --Temp Table name for loop
42 ,@OpenQuery NVARCHAR(MAX) = N'' --OPENQUERY setup for Eaglesoft data
43 ,@CompESSelect NVARCHAR(MAX) = N'' --Columns for Eaglesoft Comparison
44 ,@CompODSSelect NVARCHAR(MAX) = N'' --Columns for ODS Comparison
45
46
47 /*** Testing Variables ***/
48 ,@ESTestTable VARCHAR(100) = '[PPM].[appointment]'
49 ,@ODSTestTable VARCHAR(100) = 'appointment'
50 ,@ServerTestID VARCHAR(3) = '63'
51 ,@UATTable NVARCHAR(100) = N''
52 SET @ServerID = @ServerTestID
53
54 /*** Create Temp table for testing ***/
55 IF OBJECT_ID('tempdb..#Temp_Column_Check') IS NOT NULL EXEC('DROP TABLE #Temp_Column_Check')
56
57 CREATE TABLE #Temp_Column_Check (
58 [TableID] INT
59 ,[ODSTableName] NVARCHAR(100)
60 ,[ESTableName] NVARCHAR(100)
61 ,[ColumnIDList] NVARCHAR(MAX)
62 ,[ODSColumnNameList] NVARCHAR(MAX)
63 ,[ESColumnNameList] NVARCHAR(MAX)
64 ,[ESOpenQuery] NVARCHAR(MAX)
65 ,[ComparisonQuery] NVARCHAR(MAX)
66 ,[ESRowCount] BIGINT
67 ,[ODSRowCount] BIGINT
68 ,[ComparisonRowCount] BIGINT
69 )
70
71/*** CREATE TEMP TABLE OF DESIRED TABLES & COLUMNS ***/
72IF OBJECT_ID('tempdb..#Temp_Tables_Columns') IS NOT NULL EXEC('DROP TABLE #Temp_Tables_Columns')
73
74SELECT
75 TAB.[TableID]
76 ,TAB.[ODSSchema]
77 ,TAB.[ODSTable]
78 ,TAB.[EaglesoftSchema]
79 ,TAB.[EaglesoftTable]
80 ,COL.[ColumnID]
81 ,COL.[ODSColumn]
82 ,COL.[ODSDatatype]
83 ,COL.[EaglesoftColumn]
84 ,COL.[EaglesoftDatatype]
85 ,COL.[EaglesoftDatatypeLength]
86INTO #Temp_Tables_Columns
87FROM [DDS_ODS]..[ODS_Eaglesoft_Table_Ref] TAB
88JOIN [DDS_ODS]..[ODS_Eaglesoft_Column_Ref] COL
89 ON TAB.TableID = COL.TableID
90WHERE COL.[UAT_FLAG] = 1 --Only tables flagged to be included in the UAT test
91ORDER BY TAB.[TableID],COL.[ColumnID]
92
93/*** Generating GLOBAL VARIABLES ***/
94SELECT @TableIDMax = MAX([TableID]) --Grab last TableID
95FROM #Temp_Tables_Columns
96
97WHILE @TableIDCounter <= @TableIDMax --SET Range of available TableIDs
98BEGIN
99 SELECT @TableIDRange = @TableIDRange + CAST([TableID] AS NVARCHAR) + ','
100 FROM (SELECT DISTINCT [TableID] FROM #Temp_Tables_Columns) TID
101 WHERE [TableID] = @TableIDCounter
102 SET @TableIDCounter = @TableIDCounter + 1
103END
104
105SET @TableIDRange = LEFT(@TableIDRange, LEN(@TableIDRange)-1)
106
107SET @TableIDCounter = 1 --Reset Table Counter
108
109/*** START PRIMARY FUNCTION LOOP ***/
110WHILE @TableIDCounter <= @TableIDMax --Loop through each unique table
111BEGIN --START TABLE WHILE LOOP
112
113
114
115 /*** Reset LOOP variables ***/
116 SELECT @ColumnIDCounter = 1 --Reset Column Counter
117 ,@ColumnIDMax = 0 --Reset Column Max
118 ,@ColumnIDRange = N'' --Reset Column ID Range
119 ,@ODSColNameRange = N'' --Reset ODS Column Name Range
120 ,@ESColNameRange = N'' --Reset ES Column Name Range
121 ,@ESSelectRange = N'' --Reset ES Selection Columns
122 ,@CompESSelect = N'' --Reset ES Comparison Columns
123 ,@CompODSSelect = N'' --Reset ODS Comparison Columns
124 ,@ESTempTable = N'' --Reset Temp Table
125 ,@ODSTableName = N'' --Reset ODS Table Name
126 ,@ESTableName = N'' --Reset ES Table Name
127 ,@ESRowCount = 0 --Reset ES Row Count
128 ,@ODSRowCount = 0 --Reset ODS Row Count
129 ,@ComparisonRowCount = 0 --Reset Comparison Row Count
130 ,@OpenQuery = N'' --Reset OpenQuery
131 ,@ESRowCountQuery = N'' --Reset ES Row Count Query
132 ,@ODSRowCountQuery = N'' --Reset ODS Row Count Query
133 ,@ComparisonQuery = N'' --Reset Comparison Query
134
135 /*** Set Variable Values ***/
136 SELECT
137 @ColumnIDMax = MAX([ColumnID]) --Grab & Set last ColumnID
138 ,@ESTempTable = CONCAT(@ServerID,'_',[ODSTable]) --Temp Table name generator
139 ,@ODSTableName = CONCAT('[',[ODSSchema],'].[T_',@ServerID,'_',[ODSTable],']') --Grab ODS Schema and Table name
140 ,@ESTableName = CONCAT('[',[EaglesoftSchema],'].[',[EaglesoftTable],']') --Grab ES Schema and Table name
141 FROM #Temp_Tables_Columns
142 WHERE [TableID] = @TableIDCounter
143 GROUP BY
144 CONCAT(@ServerID,'_',[ODSTable]),
145 CONCAT('[',[ODSSchema],'].[T_',@ServerID,'_',[ODSTable],']'),
146 CONCAT('[',[EaglesoftSchema],'].[',[EaglesoftTable],']')
147
148 IF OBJECT_ID('[Dev_DDS_ODS].'+@ODSTableName+'') IS NULL --Check if the table exists within ODS
149 CONTINUE; --If table doesn't exist; go to next Loop iteration
150
151 WHILE @ColumnIDCounter <= @ColumnIDMax
152 BEGIN --START COLUMN WHILE LOOP
153
154
155
156 SELECT
157 @ColumnIDRange = @ColumnIDRange + CAST(@ColumnIDCounter AS NVARCHAR) + ',' --Append Column ID to Column ID list
158 ,@ODSColNameRange = @ODSColNameRange + '[' + CAST([ODSColumn] AS NVARCHAR) + '],' --Append column to ODS list
159 ,@ESSelectRange = @ESSelectRange + '[' + CAST([EaglesoftColumn] AS NVARCHAR) + '],' --Append column to ES List
160 ,@ESColNameRange =
161 CASE --Handle datatype exceptions
162 WHEN [EaglesoftDatatype] = 'long varchar' --Limit the [long varchar] datatype to only 800 characters
163 THEN @ESColNameRange + 'REPLACE(REPLACE(SUBSTRING(' + [EaglesoftColumn] + ',1,800),CHAR(9),'''' ''''),CHAR(13)+CHAR(10),'''';'''') AS [' + [EaglesoftColumn] + '],'
164 ELSE
165 @ESColNameRange + '[' + CAST([EaglesoftColumn] AS NVARCHAR) + '],'
166 END
167 ,@CompESSelect =
168 CASE --Handle datatype exceptions
169 WHEN [EaglesoftDatatype] = 'long varchar'
170 THEN @CompESSelect + '' --Add Nothing
171 ELSE
172 @CompESSelect + '[' + CAST([EaglesoftColumn] AS NVARCHAR) + '],'
173 END
174 ,@CompODSSelect =
175 CASE --Handle datatype exceptions
176 WHEN [EaglesoftDatatype] = 'long varchar'
177 THEN @CompODSSelect + '' --Add Nothing
178 ELSE
179 @CompODSSelect + '[' + CAST([ODSColumn] AS NVARCHAR) + '],'
180 END
181 FROM #Temp_Tables_Columns TID
182 WHERE [TableID] = @TableIDCounter
183 AND [ColumnID] = @ColumnIDCounter
184 SET @ColumnIDCounter = @ColumnIDCounter+1 --INCREASE COLUMN LOOP COUNTER
185 END --END COLUMN WHILE LOOP
186
187 SET @ColumnIDRange = LEFT(@ColumnIDRange, LEN(@ColumnIDRange)-1) --Remove trailing comma
188 SET @ODSColNameRange = LEFT(@ODSColNameRange,LEN(@ODSColNameRange)-1) --Remove trailing comma
189 SET @ESColNameRange = LEFT(@ESColNameRange,LEN(@ESColNameRange)-1) --Remove trailing comma
190 SET @ESSelectRange = LEFT(@ESSelectRange,LEN(@ESSelectRange)-1) --Remove trailing comma
191 SET @CompESSelect = LEFT(@CompESSelect,LEN(@CompESSelect)-1) --Remove trailing comma
192 SET @CompODSSelect = LEFT(@CompODSSelect,LEN(@CompODSSelect)-1) --Remove trailing comma
193
194 /*** CREATE DYNAMIC QUERIES USING VARIABLES ***/
195
196 --Create OPENQUERY script to grab data from Live Eaglesoft server
197 SET @OpenQuery =
198 N'SELECT '+@ESSelectRange+'
199 INTO [Dev_DDS_ODS_Stage]..[UAT_'+@ESTempTable+']
200 FROM OPENQUERY([C'+@ServerID+'],''SELECT '+@ESColNameRange+' FROM '+@ESTableName+' WITH (NOLOCK)''
201 )'
202
203 --Query to get row count of Eaglesoft table
204 SET @ESRowCountQuery =
205 N'SELECT @RowCount = COUNT(*)
206 FROM [Dev_DDS_ODS_Stage]..[UAT_'+@ESTempTable+']
207 ' --@ESTempTable should be in a 'UAT_99_table_name' format
208
209 --Query to get row count of ODS table
210 SET @ODSRowCountQuery =
211 N'SELECT @RowCount = COUNT(*)
212 FROM [Dev_DDS_ODS].'+@ODSTableName+'
213 ' --@ODSTableName should be in a [dbo].[table_name] format
214
215 --Query to check Eaglesoft table against ODS table for missing data
216 SET @ComparisonQuery =
217 N'SELECT @RowCount = COUNT(*)
218 FROM(
219 SELECT '+@CompESSelect+' FROM [Dev_DDS_ODS_Stage]..[UAT_'+@ESTempTable+']
220
221 EXCEPT
222
223 SELECT '+@CompODSSelect+' FROM [Dev_DDS_ODS].'+@ODSTableName+'
224 ) COMP
225 '
226
227 /*** EXECUTE DYNAMIC QUERIES ***/
228 IF OBJECT_ID('[Dev_DDS_ODS_Stage]..[UAT_'+@ESTempTable+']') IS NOT NULL EXEC('DROP TABLE [Dev_DDS_ODS_Stage]..[UAT_'+@ESTempTable+']') --DROP Temp table if already exists for Eaglesoft data
229
230 --Run query to generate Eaglesoft data into Temp table
231 EXEC sp_executesql @OpenQuery
232
233 --Run query to generate Comparison row count
234 EXEC sp_executesql @ComparisonQuery,@RowCountParam,@RowCount = @ComparisonRowCount OUTPUT
235
236 --Run ES row count query and store the results into the @ESRowCount variable
237 EXEC sp_executesql @ESRowCountQuery,@RowCountParam,@RowCount = @ESRowCount OUTPUT
238
239 --Run ODS row count query and store the results into the @ODSRowCount variable
240 EXEC sp_executesql @ODSRowCountQuery,@RowCountParam,@RowCount = @ODSRowCount OUTPUT
241
242 /*** INSERT Variables into Temp table for testing ***/
243 INSERT INTO #Temp_Column_Check
244 VALUES (
245 @TableIDCounter
246 ,@ODSTableName
247 ,@ESTableName
248 ,@ColumnIDRange
249 ,@ODSColNameRange
250 ,@ESColNameRange
251 ,@OpenQuery
252 ,@ComparisonQuery
253 ,@ESRowCount
254 ,@ODSRowCount
255 ,@ComparisonRowCount
256 )
257
258 SET @TableIDCounter = @TableIDCounter + 1 --INCREASE TABLE LOOP COUNTER
259END --END TABLE WHILE LOOP
260
261SELECT * FROM #Temp_Column_Check ORDER BY [TableID]--Check for results testing