· 7 years ago · Nov 12, 2018, 01:00 PM
1CREATE TABLE dbo.bigtable
2(
3 UpdateDate datetime,
4 PK varchar(12) PRIMARY KEY,
5 col1 varchar(100),
6 col2 int,
7 col3 varchar(20),
8 .
9 .
10 .
11 colN datetime
12);
13
14CREATE TABLE dbo.bigtable_archive
15(
16 UpdateDate datetime,
17 PK varchar(12) NOT NULL,
18 col1 varchar(100),
19 col2 int,
20 col3 varchar(20),
21 .
22 .
23 .
24 colN datetime
25);
26
27PK, UpdateDate, ColumnName, Old Value, New Value
28
29WITH
30 Combined AS
31 (
32 SELECT * FROM dbo.bigtable
33 UNION ALL
34 SELECT * FROM dbo.bigtable_archive
35 ) AS derived,
36 OldAndNew AS
37 (
38 SELECT
39 this.*,
40 OldCol1 = last.Col1,
41 OldCol2 = last.Col2,
42 ...
43 FROM
44 Combined AS this
45 OUTER APPLY
46 (
47 SELECT TOP (1)
48 *
49 FROM
50 dbo.bigtable_archive
51 WHERE
52 PK = this.PK
53 AND UpdateDate < this.UpdateDate
54 ORDER BY
55 UpdateDate DESC
56 ) AS last
57 )
58SELECT
59 t.PK,
60 t.UpdateDate,
61 x.ColumnName,
62 x.OldValue,
63 x.NewValue
64FROM
65 OldAndNew AS t
66 CROSS APPLY
67 (
68 VALUES
69 ('Col1', CAST(t.OldCol1 AS varchar(max), CAST(t.Col1 AS varchar(max))),
70 ('Col2', CAST(t.OldCol2 AS varchar(max), CAST(t.Col2 AS varchar(max))),
71 ...
72 ) AS x (ColumnName, OldValue, NewValue)
73WHERE
74 NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
75ORDER BY
76 t.PK,
77 t.UpdateDate,
78 x.ColumnName
79;
80
81create table #T
82(
83 PK varchar(12) not null,
84 UpdateDate datetime not null,
85 ColumnName nvarchar(128) not null,
86 Value varchar(max),
87 Version int not null
88);
89
90select bt.PK,
91 bt.UpdateDate,
92 (select bt.* for xml path(''), elements xsinil, type) as X
93from dbo.bigtable as bt;
94
95<UpdateDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</UpdateDate>
96<PK xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">PK1</PK>
97<col1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">c1_1_3</col1>
98<col2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">3</col2>
99<col3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
100<colN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</colN>
101
102select C1.PK,
103 C1.UpdateDate,
104 T.X.value('local-name(.)', 'nvarchar(128)') as ColumnName,
105 T.X.value('text()[1]', 'varchar(max)') as Value
106 from C1
107 cross apply C1.X.nodes('row/*') as T(X)
108
109create table #X
110(
111 PK varchar(12) not null,
112 UpdateDate datetime not null,
113 Version int not null,
114 RowData xml not null
115);
116
117create table #T
118(
119 PK varchar(12) not null,
120 UpdateDate datetime not null,
121 ColumnName nvarchar(128) not null,
122 Value varchar(max),
123 Version int not null
124);
125
126
127insert into #X(PK, UpdateDate, Version, RowData)
128select bt.PK,
129 bt.UpdateDate,
130 0,
131 (select bt.* for xml path(''), elements xsinil, type)
132from dbo.bigtable as bt
133union all
134select bt.PK,
135 bt.UpdateDate,
136 row_number() over(partition by bt.PK order by bt.UpdateDate desc),
137 (select bt.* for xml path(''), elements xsinil, type)
138from dbo.bigtable_archive as bt;
139
140with C as
141(
142 select X.PK,
143 X.UpdateDate,
144 X.Version,
145 T.C.value('local-name(.)', 'nvarchar(128)') as ColumnName,
146 T.C.value('text()[1]', 'varchar(max)') as Value
147 from #X as X
148 cross apply X.RowData.nodes('*') as T(C)
149)
150insert into #T (PK, UpdateDate, ColumnName, Value, Version)
151select C.PK,
152 C.UpdateDate,
153 C.ColumnName,
154 C.Value,
155 C.Version
156from C
157where C.ColumnName not in (N'PK', N'UpdateDate');
158
159/*
160option (querytraceon 8649);
161
162The above query might need some trick to go parallel.
163For the testdata I had on my machine exection time is 16 seconds vs 2 seconds
164https://web.archive.org/web/20180404164406/http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
165http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/
166
167*/
168
169select New.PK,
170 New.UpdateDate,
171 New.ColumnName,
172 Old.Value as OldValue,
173 New.Value as NewValue
174from #T as New
175 left outer join #T as Old
176 on Old.PK = New.PK and
177 Old.ColumnName = New.ColumnName and
178 Old.Version = New.Version + 1;
179
180CREATE TRIGGER dbo.IoI_BTA
181ON dbo.bigtable_archive
182INSTEAD OF INSERT
183AS
184BEGIN
185 IF NOT EXISTs(SELECT 1
186 FROM dbo.bigtable_archive bta
187 INNER JOIN inserted i
188 ON bta.PK = i.PK
189 AND bta.UpdateDate = i.UpdateDate)
190 BEGIN
191 INSERT INTO dbo.bigtable_archive
192 SELECT * FROM inserted;
193 END
194END
195
196CREATE TRIGGER dbo.IoI_BT
197ON dbo.bigtable
198AFTER INSERT
199AS
200BEGIN
201 IF NOT EXISTS(SELECT 1
202 FROM dbo.bigtable_archive bta
203 INNER JOIN inserted i
204 ON bta.PK = i.PK
205 AND bta.UpdateDate = i.UpdateDate)
206 BEGIN
207 INSERT INTO dbo.bigtable_archive
208 SELECT * FROM inserted;
209 END
210END
211
212SELECT * FROM bigtable;
213SELECT * FROM bigtable_archive;
214
215INSERT INTO bigtable_archive
216SELECT *
217FROM bigtable
218WHERE UpdateDate >= '20170102';
219
220INSERT INTO bigtable_archive
221SELECT *
222FROM bigtable
223WHERE UpdateDate >= '20170102';
224
225SELECT * FROM bigtable_archive;
226GO
227
228INSERT INTO dbo.bigtable_archive VALUES('20170102', 'ABC', 'C3', 1, 'C1');
229GO
230
231INSERT INTO dbo.bigtable_archive VALUES('20170102', 'ABC', 'C3', 1, 'C1');
232GO
233
234SELECT * FROM bigtable_archive;
235GO
236
237CREATE TABLE dbo.bigtable
238(UpdateDate datetime not null
239,PK varchar(12) not null
240,col1 varchar(100) null
241,col2 int null
242,col3 varchar(20) null
243,col4 datetime null
244,col5 char(20) null
245,PRIMARY KEY (PK)
246);
247
248CREATE TABLE dbo.bigtable_archive
249(UpdateDate datetime not null
250,PK varchar(12) not null
251,col1 varchar(100) null
252,col2 int null
253,col3 varchar(20) null
254,col4 datetime null
255,col5 char(20) null
256,PRIMARY KEY (PK, UpdateDate)
257);
258
259insert into dbo.bigtable values ('20170512', 'ABC', NULL, 6, 'C1', '20161223', 'closed')
260
261insert into dbo.bigtable_archive values ('20170427', 'ABC', NULL, 6, 'C1', '20160820', 'open')
262insert into dbo.bigtable_archive values ('20170315', 'ABC', NULL, 5, 'C1', '20160820', 'open')
263insert into dbo.bigtable_archive values ('20170212', 'ABC', 'C1', 1, 'C1', '20160820', 'open')
264insert into dbo.bigtable_archive values ('20170109', 'ABC', 'C1', 1, 'C1', '20160513', 'open')
265
266insert into dbo.bigtable values ('20170526', 'XYZ', 'sue', 23, 'C1', '20161223', 're-open')
267
268insert into dbo.bigtable_archive values ('20170401', 'XYZ', 'max', 12, 'C1', '20160825', 'cancel')
269insert into dbo.bigtable_archive values ('20170307', 'XYZ', 'bob', 12, 'C1', '20160825', 'cancel')
270insert into dbo.bigtable_archive values ('20170223', 'XYZ', 'bob', 12, 'C1', '20160820', 'open')
271insert into dbo.bigtable_archive values ('20170214', 'XYZ', 'bob', 12, 'C1', '20160513', 'open')
272;
273
274create table #columns
275(rid int identity(1,1)
276,PK varchar(12) not null
277,UpdateDate datetime not null
278,ColName varchar(128) not null
279,ColValue varchar(max) null
280,PRIMARY KEY (rid, PK, UpdateDate, ColName)
281);
282
283CREATE TABLE dbo.bigtable
284(UpdateDate datetime not null
285,PK varchar(12) not null
286,col1 varchar(100) null
287,col2 int null
288,col3 varchar(20) null
289,col4 datetime null
290,col5 char(20) null
291,PRIMARY KEY (PK)
292);
293
294CREATE TABLE dbo.bigtable_archive
295(UpdateDate datetime not null
296,PK varchar(12) not null
297,col1 varchar(100) null
298,col2 int null
299,col3 varchar(20) null
300,col4 datetime null
301,col5 char(20) null
302,PRIMARY KEY (PK, UpdateDate)
303);
304
305insert into dbo.bigtable values ('20170512', 'ABC', NULL, 6, 'C1', '20161223', 'closed')
306
307insert into dbo.bigtable_archive values ('20170427', 'ABC', NULL, 6, 'C1', '20160820', 'open')
308insert into dbo.bigtable_archive values ('20170315', 'ABC', NULL, 5, 'C1', '20160820', 'open')
309insert into dbo.bigtable_archive values ('20170212', 'ABC', 'C1', 1, 'C1', '20160820', 'open')
310insert into dbo.bigtable_archive values ('20170109', 'ABC', 'C1', 1, 'C1', '20160513', 'open')
311
312insert into dbo.bigtable values ('20170526', 'XYZ', 'sue', 23, 'C1', '20161223', 're-open')
313
314insert into dbo.bigtable_archive values ('20170401', 'XYZ', 'max', 12, 'C1', '20160825', 'cancel')
315insert into dbo.bigtable_archive values ('20170307', 'XYZ', 'bob', 12, 'C1', '20160825', 'cancel')
316insert into dbo.bigtable_archive values ('20170223', 'XYZ', 'bob', 12, 'C1', '20160820', 'open')
317insert into dbo.bigtable_archive values ('20170214', 'XYZ', 'bob', 12, 'C1', '20160513', 'open')
318;
319
320create table #columns
321(rid int identity(1,1)
322,PK varchar(12) not null
323,UpdateDate datetime not null
324,ColName varchar(128) not null
325,ColValue varchar(max) null
326,PRIMARY KEY (rid, PK, UpdateDate, ColName)
327);
328
329declare @columns_max varchar(max),
330 @columns_raw varchar(max),
331 @cmd varchar(max)
332
333select @columns_max = stuff((select ',isnull(convert(varchar(max),'+name+'),''NULL'') as '+name
334 from syscolumns
335 where id = object_id('dbo.bigtable')
336 and name not in ('PK','UpdateDate')
337 order by name
338 for xml path(''))
339 ,1,1,''),
340 @columns_raw = stuff((select ','+name
341 from syscolumns
342 where id = object_id('dbo.bigtable')
343 and name not in ('PK','UpdateDate')
344 order by name
345 for xml path(''))
346 ,1,1,'')
347
348
349select @cmd = '
350insert #columns (PK, UpdateDate, ColName, ColValue)
351select PK,UpdateDate,ColName,ColValue
352from
353(select PK,UpdateDate,'+@columns_max+' from bigtable
354 union all
355 select PK,UpdateDate,'+@columns_max+' from bigtable_archive
356) p
357unpivot
358 (ColValue for ColName in ('+@columns_raw+')
359) as unpvt
360order by PK, ColName, UpdateDate'
361
362--select @cmd
363
364execute(@cmd)
365
366--select * from #columns order by rid
367;
368
369select c2.PK, c2.UpdateDate, c2.ColName as ColumnName, c1.ColValue as 'Old Value', c2.ColValue as 'New Value'
370from #columns c1,
371 #columns c2
372where c2.rid = c1.rid + 1
373and c2.PK = c1.PK
374and c2.ColName = c1.ColName
375and isnull(c2.ColValue,'xxx') != isnull(c1.ColValue,'xxx')
376order by c2.UpdateDate, c2.PK, c2.ColName
377;
378
379original value varchar(max)
380------------------- -------------------
38106/10/2017 10:27:15 Jun 10 2017 10:27AM
38206/10/2017 10:27:18 Jun 10 2017 10:27AM
383
384 234.23844444 234.238
385 234.23855555 234.238
386
387 29333488.888 2.93335e+007
388 29333499.999 2.93335e+007
389
390CREATE proc getTableChanges
391 @schemaname varchar(255),
392 @tableName varchar(255)
393 as
394
395 declare @strg nvarchar(max), @colNameStrg nvarchar(max)='', @oldValueString nvarchar(max)='', @newValueString nvarchar(max)=''
396
397 set @strg = '
398 with cte as (
399
400 SELECT * , ROW_NUMBER() OVER(partition by PK ORDER BY UpdateDate) as RowNbr
401 FROM (
402
403 SELECT *
404 FROM [' + @schemaname + '].[' + @tableName + ']
405
406 UNION
407
408 SELECT *
409 FROM [' + @schemaname + '].[' + @tableName + '_archive]
410
411 ) a
412
413 )
414 '
415
416
417 SET @strg = @strg + '
418
419 SELECT a.pk, a.updateDate,
420 CASE '
421
422 DECLARE @colName varchar(255)
423 DECLARE cur CURSOR FOR
424 SELECT COLUMN_NAME
425 FROM INFORMATION_SCHEMA.COLUMNS
426 WHERE TABLE_SCHEMA = @schemaname
427 AND TABLE_NAME = @tableName
428 AND COLUMN_NAME NOT IN ('PK', 'Updatedate')
429
430 OPEN cur
431 FETCH NEXT FROM cur INTO @colName
432
433 WHILE @@FETCH_STATUS = 0
434 BEGIN
435
436 SET @colNameStrg = @colNameStrg + ' when a.' + @colName + ' <> b.' + @colName + ' then ''' + @colName + ''' '
437 SET @oldValueString = @oldValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(a.' + @colName + ' as varchar(max))'
438 SET @newValueString = @newValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(b.' + @colName + ' as varchar(max))'
439
440
441 FETCH NEXT FROM cur INTO @colName
442 END
443
444 CLOSE cur
445 DEALLOCATE cur
446
447
448 SET @colNameStrg = @colNameStrg + ' END as ColumnChanges '
449 SET @oldValueString = 'CASE ' + @oldValueString + ' END as OldValue'
450 SET @newValueString = 'CASE ' + @newValueString + ' END as NewValue'
451
452 SET @strg = @strg + @colNameStrg + ',' + @oldValueString + ',' + @newValueString
453
454 SET @strg = @strg + '
455 FROM cte a join cte b on a.PK = b.PK and a.RowNbr + 1 = b.RowNbr
456 ORDER BY a.pk, a.UpdateDate
457 '
458
459 print @strg
460
461 execute sp_executesql @strg
462
463
464 go
465
466exec getTableChanges 'dbo', 'bigTable'
467
468DECLARE @sql NVARCHAR(MAX)
469 ,@columns NVARCHAR(Max)
470 ,@table VARCHAR(200) = 'ProductCostHistory'
471 ,@Schema VARCHAR(200) = 'Production'
472 ,@Archivecolumns NVARCHAR(Max)
473 ,@ColForUnpivot NVARCHAR(Max)
474 ,@ArchiveColForUnpivot NVARCHAR(Max)
475 ,@PKCol VARCHAR(200) = 'ProductID'
476 ,@UpdatedCol VARCHAR(200) = 'modifiedDate'
477 ,@Histtable VARCHAR(200) = 'ProductListPriceHistory'
478SELECT @columns = STUFF((
479 SELECT ',CAST(p.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME(column_name)
480 FROM information_schema.columns
481 WHERE table_name = @table
482 AND column_name NOT IN (
483 @PKCol
484 ,@UpdatedCol
485 )
486 ORDER BY ORDINAL_POSITION
487 FOR XML PATH('')
488 ), 1, 1, '')
489 ,@Archivecolumns = STUFF((
490 SELECT ',CAST(p1.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME('A_' + column_name)
491 FROM information_schema.columns
492 WHERE table_name = @Histtable
493 AND column_name NOT IN (
494 @PKCol
495 ,@UpdatedCol
496 )
497 ORDER BY ORDINAL_POSITION
498 FOR XML PATH('')
499 ), 1, 1, '')
500 ,@ColForUnpivot = STUFF((
501 SELECT ',' + QUOTENAME(column_name)
502 FROM information_schema.columns
503 WHERE table_name = @table
504 AND column_name NOT IN (
505 @PKCol
506 ,@UpdatedCol
507 )
508 ORDER BY ORDINAL_POSITION
509 FOR XML PATH('')
510 ), 1, 1, '')
511 ,@ArchiveColForUnpivot = STUFF((
512 SELECT ',' + QUOTENAME('A_' + column_name)
513 FROM information_schema.columns
514 WHERE table_name = @Histtable
515 AND column_name NOT IN (
516 @PKCol
517 ,@UpdatedCol
518 )
519 ORDER BY ORDINAL_POSITION
520 FOR XML PATH('')
521 ), 1, 1, '')
522
523--SELECT @columns ,@Archivecolumns ,@ColForUnpivot
524SET @sql = N'
525 SELECT ' + @PKCol + ', ColumnName,
526 OldValue,NewValue,' + @UpdatedCol + '
527 FROM (
528 SELECT p.' + @PKCol + '
529 ,p.' + @UpdatedCol + '
530 ,' + @columns + '
531 ,' + @Archivecolumns + '
532 FROM ' + @Schema + '.' + @table + ' p
533 left JOIN ' + @Schema + '.' + @Histtable + ' p1 ON p.' + @PKCol + ' = p1.' + @PKCol + '
534
535 ) t
536 UNPIVOT (
537 OldValue
538 FOR ColumnName in (' + @ColForUnpivot + ')
539 ) up
540
541 UNPIVOT (
542 NewValue
543 FOR ColumnName1 in (' + @ArchiveColForUnpivot + ')
544 ) up1
545
546--print @sql
547EXEC (@sql)