· 8 years ago · May 08, 2018, 02:54 PM
1
2 DECLARE @TableName_t VARCHAR(30), -- Target (real) table
3 @TableName_s VARCHAR(30), -- Source table
4 @ErrCnt01 INTEGER,
5 @ErrCnt02 INTEGER,
6 @ErrCnt03 INTEGER,
7 @Hash1s NUMERIC,
8 @Hash2s NUMERIC,
9 @Hash3s NUMERIC,
10 @Hash1t NUMERIC,
11 @Hash2t NUMERIC,
12 @Hash3t NUMERIC,
13 @Message VARCHAR(80),
14 @SQL VARCHAR(80)
15
16 SET NOCOUNT ON
17
18 SELECT @TableName_i = LTRIM(RTRIM(@TableName_i)), @Prefix_i = LTRIM(RTRIM(@Prefix_i))
19
20 IF @Prefix_i IS NULL
21 SELECT @Prefix_i = 'tmp_'
22
23 IF @Key2 IS NULL
24 SELECT @Key2 = ''
25
26 IF @Key3 IS NULL
27 SELECT @Key3 = ''
28
29 IF @Col2 IS NULL
30 SELECT @Col2 = ''
31
32 IF @Col3 IS NULL
33 SELECT @Col3 = ''
34
35 SELECT @TableName_t = O.name
36 FROM sysobjects O
37 WHERE O.name = @TableName_i
38 AND charindex(substring(o.type,1,1),"U")! = 0 -- User Tables only
39
40 IF @TableName_t IS NULL
41 BEGIN
42 SELECT 'Target table ' + @TableName_i + ' does not exist' AS 'ERROR'
43 RETURN 1
44 END
45
46 SELECT @TableName_s = LTRIM(@Prefix_i + @TableName_t)
47
48 IF (SELECT count(*)
49 FROM sysobjects O
50 WHERE O.name = @TableName_s
51 AND charindex(substring(o.type,1,1),"U")! = 0) = 0
52 BEGIN
53 SELECT 'Source table ' + @TableName_s + ' does not exist' AS 'ERROR'
54 RETURN 2
55 END
56 ELSE
57 BEGIN
58 SELECT @SQL = "INSERT INTO #Counter (RECNT) SELECT COUNT(*) FROM "
59
60 CREATE TABLE #Counter (RECNT INT)
61
62 EXEC (@SQL + @TableName_s)
63
64 SELECT @Errcnt01 = RECNT
65 FROM #Counter
66
67 IF EXISTS (SELECT 1
68 FROM sysobjects
69 WHERE id = object_id('#Counter')
70 AND type = 'U')
71 DROP TABLE #Counter
72
73 IF @Errcnt01 = 0
74 BEGIN
75 SELECT 'Table ' + @TableName_s + ' exists but has no records' AS 'ERROR'
76 RETURN 3
77 END
78 END
79
80 SELECT @Errcnt01 = 0
81 ,@Errcnt02 = 0
82 ,@Errcnt03 = 0
83
84 /* Check that each record's values match the originals */
85
86 DECLARE @SQL00 VARCHAR(80), @SQL01 VARCHAR(80), @SQL02 VARCHAR(80), @SQL03 VARCHAR(80), @SQL04 VARCHAR(80)
87 , @SQL05 VARCHAR(80), @SQL06 VARCHAR(80), @SQL07 VARCHAR(80), @SQL08 VARCHAR(80)
88 , @SQL09 VARCHAR(80), @SQL10 VARCHAR(80) , @SQL11 VARCHAR(80), @SQL12 VARCHAR(80)
89
90 SELECT @SQL01 = '', @SQL02 = '', @SQL03 = '', @SQL04 = '', @SQL05 = '', @SQL06 = ''
91 ,@SQL07 = '', @SQL08 = '', @SQL09 = '', @SQL10 = '', @SQL11 = '', @SQL12 = ''
92
93 CREATE TABLE #Check (ID integer, Recnt integer)
94
95 SELECT @SQL00 = 'INSERT INTO #Check (ID, Recnt) SELECT 1, COUNT(*) FROM '
96 SELECT @SQL01 = @TableName_s + ' T1, ' + @TableName_t + ' T2 '
97 SELECT @SQL02 = ' WHERE T2.' + @Key1 + ' = T1.' + @Key1
98 IF (@Key2 != '')
99 SELECT @SQL03 = ' AND T2.' + @Key2 + ' = T1.' + @Key2
100 IF (@Key3 != '')
101 SELECT @SQL04 = ' AND T2.' + @Key3 + ' = T1.' + @Key3
102 SELECT @SQL05 = ' AND ( T2.' + @Col1 + ' <> T1.' + @Col1
103 IF (@Col2 != '')
104 SELECT @SQL06 = ' OR T2.' + @Col2 + ' <> T1.' + @Col2
105 IF (@Col3 != '')
106 SELECT @SQL07 = ' OR T2.' + @Col3 + ' <> T1.' + @Col3
107 SELECT @SQL08 = ')'
108
109 /* SELECT @SQL00 + @SQL01 + @SQL02 + @SQL03 + @SQL04 + @SQL05 + @SQL06
110 + @SQL07 + @SQL08 */
111
112 EXEC (@SQL00 + @SQL01 + @SQL02 + @SQL03 + @SQL04 + @SQL05 + @SQL06 + @SQL07 + @SQL08)
113
114 SELECT @ErrCnt01 = Recnt
115 FROM #Check
116 WHERE ID = 1
117
118 /* Check record counts are equal */
119
120 SELECT @SQL01 = '', @SQL02 = '', @SQL03 = '', @SQL04 = '', @SQL05 = '', @SQL06 = ''
121 ,@SQL07 = '', @SQL08 = '', @SQL09 = '', @SQL10 = '', @SQL11 = '', @SQL12 = ''
122
123 SELECT @SQL00 = 'INSERT INTO #Check (ID, Recnt) SELECT 2, COUNT(*) FROM ' + @TableName_s
124 EXEC (@SQL00)
125
126 SELECT @SQL00 = 'INSERT INTO #Check (ID, Recnt) SELECT 3, COUNT(*) FROM '
127 SELECT @SQL01 = @TableName_s + ' T1, ' + @TableName_t + ' T2 '
128 SELECT @SQL02 = ' WHERE T2.' + @Key1 + ' = T1.' + @Key1
129 IF (@Key2 != '')
130 SELECT @SQL03 = ' AND T2.' + @Key2 + ' = T1.' + @Key2
131 IF (@Key3 != '')
132 SELECT @SQL04 = ' AND T2.' + @Key3 + ' = T1.' + @Key3
133
134 /*
135 SELECT @SQL00 + @SQL01 + @SQL02 + @SQL03 + @SQL04
136 */
137
138 EXEC (@SQL00 + @SQL01 + @SQL02 + @SQL03 + @SQL04)
139
140 SELECT @Hash1s = Recnt
141 FROM #Check
142 WHERE ID = 2
143
144 SELECT @Hash1t = Recnt
145 FROM #Check
146 WHERE ID = 3
147
148 IF EXISTS (SELECT 1
149 FROM sysobjects
150 WHERE id = object_id('#Check')
151 AND type = 'U')
152 DROP TABLE #Check
153
154 SELECT @ErrCnt02 = @Hash1s - @Hash1t
155
156 IF @ErrCnt01 != 0
157 SELECT CONVERT(Varchar(10), @Errcnt01) + ' records had different values' AS 'ERROR'
158
159 IF ABS(@ErrCnt02) != 0
160 SELECT 'Number of records to delete differs from number of records matched' AS 'ERROR'
161
162 IF (@ErrCnt01 != 0) OR (ABS(@ErrCnt02) != 0)
163 RETURN 4 -- ERRORS
164
165 /*
166 EXEC (@SQL00 + @SQL01 + @SQL02 + @SQL03 + @SQL04 + @SQL05 + @SQL06
167 + @SQL07 + @SQL08 + @SQL09 + @SQL10 + @SQL11 + @SQL12 + @SQLzz)
168 */
169
170 SELECT @SQL = 'INSERT INTO #Counter2 (RECNT) SELECT COUNT(*) FROM '
171
172 CREATE TABLE #Counter2 (RECNT INT)
173
174 EXEC (@SQL + @TableName_s)
175
176 SELECT @Errcnt01 = RECNT
177 FROM #Counter2
178
179 IF EXISTS (SELECT 1
180 FROM sysobjects
181 WHERE id = object_id('#Counter2')
182 AND type = 'U')
183 DROP TABLE #Counter2
184
185 SELECT CONVERT(VARCHAR(10),@ErrCnt01) + ' records verified in ' + @TableName_t
186
187 SET NOCOUNT OFF
188
189 RETURN 0