· 8 years ago · Dec 14, 2017, 10:26 PM
1use MyDatabase
2go
3
4if exists( select *
5 from sys.objects
6 where [object_id] = object_id( N'dbo.spInsertUpdateStageIntoProdDynamicSql' ) and
7 [type] in ( N'P' ) )
8begin
9 drop proc dbo.spInsertUpdateStageIntoProdDynamicSql ;
10 print 'Dropped procedure dbo.spInsertUpdateStageIntoProdDynamicSql SUCCESSFULLY! At time ' + convert( varchar, getdate(), 126 ) ;
11end ;
12go
13
14create proc dbo.spInsertUpdateStageIntoProdDynamicSql
15 @TargetTable nvarchar( 116 ),
16 @DebugOption varchar( 100 ),
17 @SpoofDateTime datetime
18as
19/* who when what
20petervandivier 2015-09-15 create proc to use dynamic sql to merge stage tables into prod for
21petervandivier 2015-09-23 added @TargetTable to results logging
22*/
23begin
24 set nocount on ;
25 set transaction isolation level read committed ;
26
27 declare
28 @RetMsg varchar( max ) = '',
29 @ErrMsg varchar( max ) = '',
30 @CountRows int,
31 @ProcName varchar( 100 ) = 'dbo.spInsertUpdateStageIntoProdDynamicSql',
32 @ExecDateTime datetime = getdate(),
33 @ToggleOffClause nvarchar( max ) = N'',
34 @UpdateRowsClause nvarchar( max ) = N'',
35 @InsertRowsClause nvarchar( max ) = N'',
36 @StageTable nvarchar( 255 ) = N'staging.' + @TargetTable,
37 @DboTable nvarchar( 255 ) = N'dbo.' + @TargetTable,
38 @Lb nchar( 1 ) = char( 10 ),
39 @Tab nchar( 1 ) = char( 9 ) ;
40
41 declare @ResultArray table
42 (
43 CountRows int,
44 Step varchar( 100 ) unique,
45 CommandText varchar( max)
46 ) ;
47
48 -- SpoofDateTime accepted for prior pd. cursor
49 select
50 @ExecDateTime = coalesce( @SpoofDateTime, @ExecDateTime ),
51 @RetMsg = 'Variables initialized SUCCESSFULLY. Parameter validation will begin. ' + @Lb + @Lb ;
52
53 insert dbo.Log
54 (
55 Step,
56 Process,
57 CountRows,
58 Notes,
59 CommandText,
60 InsertedBy,
61 InsertDatetime,
62 LastUpdateBy,
63 LastUpdateDatetime
64 )
65 select
66 'ParameterValidation',
67 @ProcName,
68 null,
69 @TargetTable,
70 '@TargetTable:=' + isnull( @TargetTable, 'null_val' ) + ';@DebugOption:=' + isnull( @DebugOption, 'null_val' ) + ';@SpoofDate:=' + isnull( '''' + convert( varchar, @SpoofDateTime, 126 ) + '''', 'null_val' ),
71 @ProcName,
72 @ExecDateTime,
73 @ProcName,
74 @ExecDateTime ;
75
76-- Check if BOTH target and source tables exist, else escape proc
77 if not( exists( select *
78 from sys.tables
79 where [object_id] = object_id( @StageTable ) ) and
80 exists( select *
81 from sys.tables
82 where [object_id] = object_id( @DboTable ) ) )
83 begin
84 select @ErrMsg += 'One or both of target tables ' + @DboTable + ' and ' + @StageTable + ' does not exist current Database ' + db_name() + N'. Execution ABORTED!' + @Lb + @Lb ;
85 goto QuitWithFailure ;
86 end
87-- Check if BOTH target and source tables have Primary Keys for merge / upsert, else escape proc
88 else if not( exists( select *
89 from sys.tables t
90 join information_schema.key_column_usage kcu on kcu.TABLE_NAME = t.name and
91 kcu.TABLE_SCHEMA = 'stage'
92 where t.[object_id] = object_id( @StageTable ) ) and
93 exists( select *
94 from sys.tables t
95 join information_schema.key_column_usage kcu on kcu.TABLE_NAME = t.name and
96 kcu.TABLE_SCHEMA = 'dbo'
97 where t.[object_id] = object_id( @DboTable ) ) )
98 begin
99 select @ErrMsg += 'One or both of target tables ' + @DboTable + ' and ' + @StageTable + ' is MISSING a PRIMARY KEY declaration across one or more columns. Execution ABORTED!' + @Lb + @Lb ;
100 goto QuitWithFailure ;
101 end
102 else
103 begin
104 select @RetMsg += 'Parameters validated SUCCESSFULLY. Dynamic sql assignation will begin.' + @Lb + @Lb ;
105
106 /****************************************************/
107 /* Toggle off rows that no longer exist in staging */
108 /****************************************************/
109 select @ToggleOffClause =
110 'with TableIdsRemainingFromYesterday as ' + @Lb +
111 -- get all Table Identitys from the target where the PK is found on the source
112 ' ( select TableIdentity ' + @Lb +
113 ' from ' + @DboTable + ' as MyTarget ' + @Lb +
114 -- trim last "and " off PK match
115 ' inner join ' + @StageTable + ' as MySource on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + @Lb +
116 ' where IsCurrentRow = ''Y'' ) ' + @Lb +
117 'update ' + @DboTable + ' set ' + @Lb +
118 @Tab + ' Revision = Revision + 1, ' + @Lb +
119 @Tab + ' LastUpdateBy = ''' + @ProcName + ''', ' + @Lb +
120 @Tab + ' LastUpdateDateTime = ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ' + @Lb +
121 @Tab + ' IsCurrentRow = ''N'' ' + @Lb +
122 'from ' + @DboTable + ' as MyTarget ' + @Lb +
123 ' where MyTarget.IsCurrentRow = ''Y'' and ' + @Lb +
124-- when the PK ( and therefore the 1:1 corresponding ) is not on the Source, soft-delete it from target
125 @Tab + ' MyTarget.TableIdentity not in ( select TableIdentity from TableIdsRemainingFromYesterday ) ; '
126 from sysobjects so
127 cross apply
128-- match on stage primary key = dbo primary key
129 (
130 select
131 @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
132 from INFORMATION_SCHEMA.COLUMNS c
133 where TABLE_SCHEMA = 'stage' and
134 TABLE_NAME = @TargetTable and
135 exists( select *
136 from information_schema.key_column_usage kcu
137 where TABLE_SCHEMA = 'stage' and
138 TABLE_NAME = @TargetTable and
139 COLUMN_NAME = c.COLUMN_NAME )
140 ) pkm ( PrimaryKeyMatch ) ;
141
142 /****************************************************/
143 /* Update rows that already exist AND have changed */
144 /****************************************************/
145 select @UpdateRowsClause =
146 'update ' + @DboTable + ' set ' +
147 wmc.WhenMatchedClause + @Lb +
148 @Tab + ' Revision = Revision + 1, ' + @Lb +
149 @Tab + ' LastUpdateBy = ''' + @ProcName + ''', ' + @Lb +
150 @Tab + ' LastUpdateDateTime = ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ' + @Lb +
151 @Tab + ' IsCurrentRow = ''Y'' ' + @Lb +
152 'from ' + @DboTable + ' as MyTarget ' + @Lb +
153-- trim last "and " off PK match
154 ' inner join ' + @StageTable + ' as MySource on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + @Lb +
155 ' where ' + wneoc.WhereNotEqualsOrClause + @Lb +
156 -- rows re-entering staging report that had previously left
157 @Tab + ' MyTarget.IsCurrentRow != ''Y'' ; '
158 from sysobjects so
159-- match on stage primary key = dbo primary key
160 cross apply
161 (
162 select
163 @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
164 from INFORMATION_SCHEMA.COLUMNS c
165 where TABLE_SCHEMA = 'stage' and
166 TABLE_NAME = @TargetTable and
167 exists( select COLUMN_NAME
168 from information_schema.key_column_usage kcu
169 where TABLE_SCHEMA = 'stage' and
170 TABLE_NAME = @TargetTable and
171 COLUMN_NAME = c.COLUMN_NAME )
172 ) pkm ( PrimaryKeyMatch )
173-- WhenMatchedClause
174 cross apply
175 (
176 select -- top( 10 ) -- top 10 for debugging. varchar( max ) can only display in print window to 4k chars
177 @Lb + @Tab + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ','
178 from INFORMATION_SCHEMA.COLUMNS c
179 where TABLE_SCHEMA = 'stage' and
180 TABLE_NAME = @TargetTable and
181 -- ignore table primary key
182 not exists( select COLUMN_NAME
183 from information_schema.key_column_usage kcu
184 where TABLE_SCHEMA = 'stage' and
185 TABLE_NAME = @TargetTable and
186 COLUMN_NAME = c.COLUMN_NAME )
187 order by c.ORDINAL_POSITION
188 for xml path( '' )
189 ) wmc ( WhenMatchedClause )
190-- WhereNotEqualsOrClause
191 cross apply
192 (
193 select -- top( 10 )
194 @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' != MySource.' + quotename( COLUMN_NAME ) + ' or'
195 from INFORMATION_SCHEMA.COLUMNS c
196 where TABLE_SCHEMA = 'stage' and
197 TABLE_NAME = @TargetTable and
198 -- ignore table primary key
199 not exists( select COLUMN_NAME
200 from information_schema.key_column_usage kcu
201 where TABLE_SCHEMA = 'stage' and
202 TABLE_NAME = @TargetTable and
203 COLUMN_NAME = c.COLUMN_NAME )
204 order by c.ORDINAL_POSITION
205 for xml path( '' )
206 ) wneoc ( WhereNotEqualsOrClause ) ;
207
208 /********************/
209 /* Insert New Rows */
210 /********************/
211 select @InsertRowsClause =
212 'insert ' + @DboTable + @Lb + '( ' +
213 @Tab + ac.AllColumns + @Lb +
214 @Tab + ' InsertDateTime, InsertedBy, LastUpdateDateTime, LastUpdateBy ) ' + @Lb +
215 ' select '+ ac.AllColumns + @Lb +
216 @Tab + '''' + convert( varchar, @ExecDateTime, 126 ) + ''', ''' + @ProcName + ''', ''' + convert( varchar, @ExecDateTime, 126 ) + ''', ''' + @ProcName + '''' + @Lb +
217 ' from ' + @StageTable + @Lb +
218 ' where not exists ( select 1 from ' + @StageTable + ' as MySource join ' + @DboTable + ' as MyTarget on ' + left( pkm.PrimaryKeyMatch, len( pkm.PrimaryKeyMatch ) - 4 ) + ' ) ;'
219 from sysobjects so
220-- All non-metadata columns
221 cross apply
222 (
223 select -- top( 10 )
224 @Lb + @Tab + quotename( COLUMN_NAME ) + ','
225 from INFORMATION_SCHEMA.COLUMNS c
226 where TABLE_SCHEMA = 'stage' and
227 TABLE_NAME = @TargetTable
228 order by c.ORDINAL_POSITION
229 for xml path( '' )
230 ) ac ( AllColumns )
231 cross apply
232-- match on stage primary key = dbo primary key
233 (
234 select
235 @Lb + @Tab + 'MyTarget.' + quotename( COLUMN_NAME ) + ' = MySource.' + quotename( COLUMN_NAME ) + ' and '
236 from INFORMATION_SCHEMA.COLUMNS c
237 where TABLE_SCHEMA = 'stage' and
238 TABLE_NAME = @TargetTable and
239 exists( select COLUMN_NAME
240 from information_schema.key_column_usage kcu
241 where TABLE_SCHEMA = 'stage' and
242 TABLE_NAME = @TargetTable and
243 COLUMN_NAME = c.COLUMN_NAME )
244 ) pkm ( PrimaryKeyMatch ) ;
245
246 /****************/
247 /* Debug Step */
248 /****************/
249 if @DebugOption is not null
250 begin
251 select @RetMsg += 'DebugOption was selected. Input Option of ' + isnull( nullif( @DebugOption, '' ), '{blank_string}' ) + '. Execution step will be skipped.' ;
252
253 if charindex( '1', @ToggleOffClause ) <> 0 print @ToggleOffClause ;
254 if charindex( '2', @UpdateRowsClause ) <> 0 print @UpdateRowsClause ;
255 if charindex( '3', @InsertRowsClause ) <> 0 print @InsertRowsClause ;
256
257 goto QuitWithSuccess ;
258 end ;
259
260 select @RetMsg += 'Dynamic sql assignation completed SUCCESSFULLY. Execution try will begin.' + @Lb + @Lb ;
261 /********************************/
262 /* Begin Execution Try Series */
263 /********************************/
264 begin try
265 exec sp_executesql @ToggleOffClause ;
266 set @CountRows = @@rowcount ;
267 insert @ResultArray
268 ( CountRows, Step, CommandText )
269 select
270 @CountRows, 'ToggleOff', @ToggleOffClause ;
271
272 select @RetMsg += 'Toggle off step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
273 end try
274 begin catch
275 select @ErrMsg += error_message() + @Lb + @Lb ;
276 end catch ;
277
278 begin try
279 exec sp_executesql @UpdateRowsClause ;
280 set @CountRows = @@rowcount ;
281 insert @ResultArray
282 ( CountRows, Step, CommandText )
283 select
284 @CountRows, 'UpdateRows', @UpdateRowsClause ;
285
286 select @RetMsg += 'Update Rows step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
287 end try
288 begin catch
289 select @ErrMsg += error_message() + @Lb + @Lb ;
290 end catch ;
291
292 begin try
293 exec sp_executesql @InsertRowsClause ;
294 set @CountRows = @@rowcount ;
295 insert @ResultArray
296 ( CountRows, Step, CommandText )
297 select
298 @CountRows, 'InsertRows', @InsertRowsClause ;
299
300 select @RetMsg += 'Insert Rows step completed SUCCESSFULLY! ' + convert( varchar, @CountRows ) + ' row(s) affected. ' + @Lb + @Lb ;
301 end try
302 begin catch
303 select @ErrMsg += error_message() + @Lb + @Lb ;
304 end catch ;
305
306 select @RetMsg += @ProcName + N' executed SUCCESSFULLY on target table ' + @TargetTable + '. Results will be logged to [dbo].[Log].'+ @Lb + @Lb ;
307
308 end ;
309
310LogResults:
311 insert dbo.Log
312 (
313 Step,
314 Process,
315 CountRows,
316 Notes,
317 CommandText,
318 InsertedBy,
319 InsertDatetime,
320 LastUpdateBy,
321 LastUpdateDatetime
322 )
323 select
324 ra.Step,
325 @ProcName,
326 ra.CountRows,
327 @TargetTable,
328 ra.CommandText,
329 @ProcName,
330 @ExecDateTime,
331 @ProcName,
332 @ExecDateTime
333 from @ResultArray ra
334 union all
335 select
336 'LogResults',
337 @ProcName,
338 null,
339 @TargetTable,
340 @RetMsg,
341 @ProcName,
342 @ExecDateTime,
343 @ProcName,
344 @ExecDateTime ;
345
346 goto QuitWithSuccess ;
347
348-- Raiserror & return failure
349QuitWithFailure:
350 insert dbo.Log
351 (
352 Step,
353 Process,
354 CountRows,
355 Notes,
356 ErrorMessage,
357 CommandText,
358 InsertedBy,
359 InsertDatetime,
360 LastUpdateBy,
361 LastUpdateDatetime
362 )
363 select
364 'QuitWithFailure',
365 @ProcName,
366 null,
367 @RetMsg,
368 @ErrMsg,
369 null,
370 @ProcName,
371 @ExecDateTime,
372 @ProcName,
373 @ExecDateTime ;
374 print @RetMsg ;
375 raiserror( @ErrMsg, 11, -1 ) ;
376 return -1 ;
377 goto EndSave ;
378
379-- Print results & return success
380QuitWithSuccess:
381 print @RetMsg ;
382 return 0 ;
383
384EndSave:
385end ;
386go
387
388if exists( select *
389 from sys.objects
390 where [object_id] = object_id( N'dbo.spInsertUpdateStageIntoProdDynamicSql' ) and
391 [type] in ( N'P' ) )
392 print 'Create procedure dbo.spInsertUpdateStageIntoProdDynamicSql SUCCESSFULLY! At time ' + convert( varchar, getdate(), 126 ) ;
393else
394 print 'Create procedure dbo.spInsertUpdateStageIntoProdDynamicSql FAILED! At time ' + convert( varchar, getdate(), 126 ) ;
395go
396
397/*
398
399exec dbo.spInsertUpdateStageIntoProdDynamicSql N'TestTable', '2015-09-02', null ;
400select * from [dbo].[Log] ;
401
402
403truncate table [dbo].[Log] ;
404
405*/
406
407SET NOCOUNT ON;
408USE tempdb;
409
410-- Create a simple table
411
412IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
413BEGIN
414 DROP TABLE dbo.T1;
415END
416
417CREATE TABLE dbo.T1
418(
419 T1_ID int NOT NULL
420 CONSTRAINT PK_T1
421 PRIMARY KEY CLUSTERED
422 IDENTITY(1,1)
423 , SomeData varchar(255) NULL
424);
425GO
426
427INSERT INTO dbo.T1 (SomeData)
428VALUES ('asdfasdf');
429
430GO
431
432BEGIN TRANSACTION;
433BEGIN TRY
434 IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
435 BEGIN
436 DROP TABLE dbo.T2;
437 END
438 CREATE TABLE dbo.T2
439 (
440 T1_ID int NOT NULL
441 CONSTRAINT PK_T2
442 PRIMARY KEY CLUSTERED
443 IDENTITY(1,1)
444 , SomeData varchar(255) NULL
445 );
446 INSERT INTO dbo.T2 (SomeData)
447 VALUES ('asdfasdf1');
448 TRUNCATE TABLE dbo.T1;
449 ALTER TABLE dbo.T2 SWITCH TO dbo.T1;
450 COMMIT TRANSACTION;
451END TRY
452BEGIN CATCH
453 IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
454END CATCH
455GO