I have a number of large tables, each with >300 columns. The application I am using creates "archives" of changed rows by making a copy of the current row in a secondary table.
Consider a trivial example:
CREATE TABLE dbo.bigtable
(
UpdateDate datetime,
PK varchar(12) PRIMARY KEY,
col1 varchar(100),
col2 int,
col3 varchar(20),
.
.
.
colN datetime
);
Archive table:
CREATE TABLE dbo.bigtable_archive
(
UpdateDate datetime,
PK varchar(12) NOT NULL,
col1 varchar(100),
col2 int,
col3 varchar(20),
.
.
.
colN datetime
);
Before any updates are executed on dbo.bigtable
, a copy of the row is created in dbo.bigtable_archive
, then dbo.bigtable.UpdateDate
is updated with the current date.
Therefore UNION
ing the two tables together & grouping by PK
creates a timeline of changes, when ordered by UpdateDate
.
I wish to create a report detailing the differences between rows, ordered by UpdateDate
, grouped by PK
, in the following format:
PK, UpdateDate, ColumnName, Old Value, New Value
Old Value
and New Value
can be the relevant columns cast to a VARCHAR(MAX)
(there are no TEXT
or BYTE
columns involved), as I do not need to do any post-processing of the values themselves.
At the moment I can't think of a sane way of doing this for a large amount of columns, without resorting to generating the queries programmatically – I may have to do this.
Open to lots of ideas, so I'll add a bounty to the question after 2 days.
Best Answer
This is not going to look pretty, especially given the more than 300 columns and unavailability of
LAG
, nor is it likely to perform exceedingly well, but just as something to start with, I would try the following approach:UNION
the two tables.OUTER APPLY
+TOP (1)
as a poor man'sLAG
).varchar(max)
and unpivot them in pairs, i.e. the current and the previous value (CROSS APPLY (VALUES ...)
works well for this operation).The Transact-SQL of the above as I see it: