Как определить измененные значения, используя временную таблицу SQL Server?
У меня есть таблица SQL Azure, и я включил новую функцию временных таблиц (впервые в SQL Server 2016 и SQL Azure v12). Эта функция создает другую таблицу для отслеживания всех изменений в основной таблице (в нижней части моего вопроса я включил ссылку на документы о временных таблицах). Вы можете использовать специальный язык запросов, чтобы получить эту историю. Обратите внимание на FOR SYSTEM_TIME ALL в следующем запросе:
SELECT
ValidFrom
, ValidTo
, ShiftId
, TradeDate
, StatusID
, [LastActionDate]
, [OwnerUserID]
, [WorkerUserID]
, [WorkerEmail]
, [Archived]
FROM [KrisisShifts_ShiftTrade]
FOR SYSTEM_TIME ALL
WHERE [ShiftID] = 27
ORDER BY ValidTo Desc
Результирующий набор выглядит так:
ValidFrom ValidTo ShiftId TradeDate StatusID LastActionDate OwnerUserID WorkerUserID WorkerEmail Archived
--------------------------- --------------------------- ----------- ---------- ----------- ----------------------- ----------- ------------ -------------------------------------------------- --------
2017-06-21 00:26:44.51 9999-12-31 23:59:59.99 27 2017-01-27 3 2017-01-09 16:23:39.760 45 34 test@hotmail.com 1
2017-06-21 00:19:35.57 2017-06-21 00:26:44.51 27 2017-01-27 2 2017-01-09 16:23:39.760 45 34 test@hotmail.com 1
2017-06-21 00:19:16.25 2017-06-21 00:19:35.57 27 2017-01-28 3 2017-01-09 16:23:39.760 45 34 test@hotmail.com 1
Использование SYSTEM_TIME FOR ALL Временная таблица возвращает текущую запись из первичной таблицы, которая является первой, а остальные записи являются предыдущими версиями этой записи, хранящейся в таблице отслеживания. (вы можете видеть столбцы validFrom и ValidTo, очевидно, время, когда запись была текущей записью) В этом случае таблица отслеживания, в которой хранятся исторические записи, называется KrisisShifts_ShiftTrade_History
ЧТО Я ХОЧУ:
Я хочу построить запрос, который просто выделяет изменения, сделанные в каждой исторической точке. Обратите внимание, что вторая запись имеет другой StatusID и что третья запись имеет другую TradeDate
Я хочу создать результирующий набор, как показано ниже (я представляю, что буду игнорировать первую или текущую запись, потому что она явно не поменялась):
Желаемый результат:
ShiftId Column Value ValidFrom ValidTo
---------- ------------- ------------------- --------------------------- --------------------------
27 StatusId 2 2017-06-21 00:19:35.57 2017-06-21 00:26:44.51
27 TradeDate 2017-01-28 2017-06-21 00:19:35.57 2017-06-21 00:26:44.51
Я не уверен, как это сделать. Или я открыт для другого решения. Я хочу иметь возможность быстро просматривать изменения для каждой записи по сравнению с исходной записью.
Я попытался отменить результаты, чтобы сравнить их, но мне не удалось заставить это работать, потому что идентификатор смены одинаков для каждой строки. Я хотел бы показать больше работы здесь, но я действительно застрял.
РЕДАКТИРОВАТЬ 1:
Мне удалось выделить изменения только для одного столбца в следующем запросе с помощью lag(). Я мог бы объединить этот запрос с аналогичным запросом для каждого столбца, который я хочу отслеживать, однако, это большая работа, и его нужно построить для каждой таблицы. Есть ли способ сделать это динамически, чтобы он автоматически обнаруживал столбцы?
Запрос истории изменений StatusID:(Я изолирую записи до shiftId 27 только для тестирования)
SELECT 'SHIFT STATUS' as ColumnName, t1.RecVersion, t1.ShiftID, t1.ValidFrom, t1.ValidTo, t1.StatusId
, (SELECT [Title] FROM [dbo].[KrisisShifts_Status] WHERE [dbo].[KrisisShifts_Status].[StatusID] = t1.StatusId) AS RecStatus
FROM
(SELECT TOP 100 PERCENT
ROW_NUMBER() OVER(PARTITION BY ShiftId ORDER BY ValidTo ASC) AS RecVersion -- reverse sorting the ValidTo date gives "version count" to column changes
, t2.ValidTo
, t2.ValidFrom
, t2.ShiftID
, t2.StatusId
, LAG(StatusId,1,0) OVER (ORDER BY ValidTo DESC) AS PrevStatusId
FROM [KrisisShifts_ShiftTrade]
FOR SYSTEM_TIME ALL AS t2
ORDER BY t2.ValidTo Desc
) AS t1
WHERE
(t1.StatusId <> t1.PrevStatusId)
AND
SHIFTID = 27
ORDER BY t1.ValidTo DESC
РЕЗУЛЬТАТЫ запроса:
ColumnName RecVersion ShiftID ValidFrom ValidTo StatusId RecStatus
------------ -------------------- ----------- --------------------------- --------------------------- ----------- --------------------------------------------------
SHIFT STATUS 3 27 2017-06-21 00:26:44.51 2017-06-25 14:09:32.37 3 Confirmed
SHIFT STATUS 2 27 2017-06-21 00:19:35.57 2017-06-21 00:26:44.51 2 Reserved
SHIFT STATUS 1 27 2017-06-21 00:19:16.25 2017-06-21 00:19:35.57 3 Confirmed
КОНЕЦ РЕДАКТИРОВАНИЯ 1:
ВОПРОС:
Может ли кто-нибудь помочь мне изолировать только измененные данные в столбцах от предыдущей записи для каждого shiftId в наборе результатов временной таблицы?
заранее спасибо
РЕДАКТИРОВАТЬ № 2:
Ниже приведен список всех столбцов, которые я хочу "наблюдать за изменениями" из этой таблицы:
] ] [LastModifiedByUserID] [Архивированный] [Обновленная дата]
КОНЕЦ РЕДАКТИРОВАНИЯ 2:
ПРИМЕЧАНИЕ НА НОВОМ ТЭГЕ:
Я создал новый тег для временных таблиц, поскольку его нет. Ниже приведено описание их, если кто-то с большей репутацией хочет добавить его к деталям тега.
7 ответов
Вы также можете использовать CROSS APPLY в UNPIVOT,
Следует отметить, что ValidFrom а также ValidTo ссылаться на достоверность самой версии строки, не обязательно значение столбца. Я считаю, что это то, что вы просите, но это может сбить с толку.
WITH T
AS (SELECT ValidFrom,
ValidTo,
ShiftId,
TradeDate,
StatusID,
LastActionDate,
OwnerUserID,
WorkerUserID,
WorkerEmail,
Archived,
nextTradeDate = LEAD(TradeDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextStatusID = LEAD(StatusID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextLastActionDate = LEAD(LastActionDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextOwnerUserID = LEAD(OwnerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextWorkerUserID = LEAD(WorkerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextWorkerEmail = LEAD(WorkerEmail) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextArchived = LEAD(Archived) OVER (PARTITION BY ShiftId ORDER BY ValidFrom)
FROM KrisisShifts_ShiftTrade)
SELECT ShiftId,
Colname AS [Column],
value,
ValidFrom,
ValidTo
FROM T
CROSS APPLY ( VALUES
('TradeDate', CAST(TradeDate AS NVARCHAR(4000)), CAST(nextTradeDate AS NVARCHAR(4000))),
('StatusID', CAST(StatusID AS NVARCHAR(4000)), CAST(nextStatusID AS NVARCHAR(4000))),
('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000)), CAST(nextLastActionDate AS NVARCHAR(4000))),
('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000)), CAST(nextOwnerUserID AS NVARCHAR(4000))),
('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000)), CAST(nextWorkerUserID AS NVARCHAR(4000))),
('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000)), CAST(nextWorkerEmail AS NVARCHAR(4000))),
('Archived', CAST(Archived AS NVARCHAR(4000)), CAST(nextArchived AS NVARCHAR(4000)))
) CA(Colname, value, nextvalue)
WHERE EXISTS(SELECT value
EXCEPT
SELECT nextvalue)
AND ValidTo <> '9999-12-31 23:59:59'
ORDER BY ShiftId,
[Column],
ValidFrom;
Если вы хотите получить достоверность на уровне столбца, вы можете использовать ( Демо)
WITH T1 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ShiftId, colname ORDER BY ValidFrom)
- ROW_NUMBER() OVER (PARTITION BY ShiftId, colname, Colvalue ORDER BY ValidFrom) AS Grp,
IIF(DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue) +
DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue DESC) = 2, 0,1) AS HasChanges
FROM KrisisShifts_ShiftTrade
CROSS APPLY ( VALUES
('TradeDate', CAST(TradeDate AS NVARCHAR(4000))),
('StatusID', CAST(StatusID AS NVARCHAR(4000))),
('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000))),
('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000))),
('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000))),
('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000))),
('Archived', CAST(Archived AS NVARCHAR(4000)))
) CA(Colname, Colvalue)
)
SELECT ShiftId, colname, Colvalue, MIN(ValidFrom) AS ValidFrom, MAX(ValidTo) AS ValidTo
FROM T1
WHERE HasChanges = 1
GROUP BY ShiftId, colname, Colvalue, Grp
ORDER BY ShiftId,
colname,
ValidFrom;
Это, конечно, не самый эффективный способ, но соответствует требованию
Есть ли способ сделать это динамически, чтобы он автоматически обнаруживал столбцы?
WITH k
AS (SELECT *,
ROW_NUMBER() OVER (PARTITION BY ShiftId ORDER BY ValidFrom) AS _RN
FROM KrisisShifts_ShiftTrade
/*FOR SYSTEM_TIME ALL*/
),
T
AS (SELECT k.*,
_colname = n.n.value('local-name(.)[1]', 'sysname'),
_colvalue = n.n.value('text()[1]', 'nvarchar(4000)')
FROM k
CROSS apply (SELECT (SELECT k.*
FOR xml path('row'), elements xsinil, type)) ca(x)
CROSS APPLY x.nodes('/row/*[not(self::_RN or self::ValidFrom or self::ValidTo)]') n(n))
SELECT T.ShiftId,
T._colname AS [Column],
T._colvalue AS value,
t.ValidFrom,
T.ValidTo
FROM T T
INNER JOIN T Tnext
ON Tnext._RN = T._RN + 1
AND T.ShiftId = Tnext.ShiftId
AND T._colname = Tnext._colname
WHERE EXISTS(SELECT T._colvalue
EXCEPT
SELECT Tnext._colvalue)
ORDER BY ShiftId,
[Column],
ValidFrom;
метод
Предложил бы использовать хранимую процедуру, которая перебирает строки с помощью курсора и создает результаты во временной таблице. (Поскольку здесь имеется управляемое количество столбцов, я бы предложил выполнить сравнение значений каждого столбца вручную, а не пытаться делать это динамически, поскольку последний будет более сложным.)
демонстрация
Rextester demo: http://rextester.com/EEELN72555
Хранимая процедура SQL
CREATE PROCEDURE GetChanges(@RequestedShiftID INT)
AS
BEGIN
DECLARE @ValidFrom DATETIME, @ValidTo DATETIME, @TradeDate DATETIME;
DECLARE @PrevTradeDate DATETIME, @LastActionDate DATETIME;
DECLARE @PrevLastActionDate DATETIME;
DECLARE @ShiftId INT, @StatusID INT, @PrevStatusID INT, @OwnerUserID INT;
DECLARE @PrevOwnerUserID INT, @WorkerUserID INT, @PrevWorkerUserID INT;
DECLARE @Archived INT, @PrevArchived INT;
DECLARE @WorkerEmail VARCHAR(MAX), @PrevWorkerEmail VARCHAR(MAX);
CREATE TABLE #Results (Id INT NOT NULL IDENTITY (1,1) PRIMARY KEY, ShiftId INT,
[Column] VARCHAR(255), Value VARCHAR(MAX),
ValidFrom DATETIME, ValidTo DATETIME);
DECLARE cur CURSOR FOR
SELECT
ValidFrom
, ValidTo
, ShiftId
, TradeDate
, StatusID
, [LastActionDate]
, [OwnerUserID]
, [WorkerUserID]
, [WorkerEmail]
, [Archived]
FROM [KrisisShifts_ShiftTrade]
FOR SYSTEM_TIME ALL
WHERE [ShiftID] = @RequestedShiftID
ORDER BY ValidTo Desc;
OPEN cur;
FETCH NEXT FROM cur INTO
@ValidFrom
, @ValidTo
, @ShiftId
, @TradeDate
, @StatusID
, @LastActionDate
, @OwnerUserID
, @WorkerUserID
, @WorkerEmail
, @Archived;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PrevTradeDate = @TradeDate;
SET @PrevStatusID = @StatusID;
SET @PrevLastActionDate = @LastActionDate;
SET @PrevOwnerUserID = @OwnerUserID;
SET @PrevWorkerUserID = @WorkerUserID;
SET @PrevWorkerEmail = @WorkerEmail;
SET @PrevArchived = @Archived;
FETCH NEXT FROM cur INTO
@ValidFrom
, @ValidTo
, @ShiftId
, @TradeDate
, @StatusID
, @LastActionDate
, @OwnerUserID
, @WorkerUserID
, @WorkerEmail
, @Archived;
IF @TradeDate <> @PrevTradeDate
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'TradeDate', @TradeDate, @ValidFrom, @ValidTo);
IF @StatusID <> @PrevStatusID
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'StatusID', @StatusID, @ValidFrom, @ValidTo);
IF @LastActionDate <> @PrevLastActionDate
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'LastActionDate', @LastActionDate, @ValidFrom, @ValidTo);
IF @OwnerUserID <> @PrevOwnerUserID
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'OwnerUserID', @OwnerUserID, @ValidFrom, @ValidTo);
IF @WorkerUserID <> @PrevWorkerUserID
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'WorkerUserID', @WorkerUserID, @ValidFrom, @ValidTo);
IF @WorkerEmail <> @PrevWorkerEmail
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'WorkerEmail', @WorkerEmail, @ValidFrom, @ValidTo);
IF @Archived <> @PrevArchived
INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
VALUES (@ShiftId, 'WorkerEmail', @WorkerEmail, @ValidFrom, @ValidTo);
END
CLOSE cur;
DEALLOCATE cur;
SELECT ShiftId, [Column], Value, ValidFrom, ValidTo
FROM #Results
ORDER BY Id
END;
Примечание. Выше приведены только столбцы, которые были в примере в вопросе. Список столбцов, которые могли измениться в недавнем редактировании, был шире, чем этот, но другие, конечно, можно добавить точно так же.
Что касается решения @Martin Smith "WITH T" (ответ дан 01 июля '17 в 19:31), не было достаточно тестовых данных. Мы можем изменить тестовые данные, чтобы получить обновление OwnerUserID (с 55 до 45) в 2017-06-21 00:22:22 (в середине существующего диапазона для (StatusID = 2)):
VALUES
('2017-06-21 00:26:44', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1)
Тогда результаты следующие:
ShiftId Column value ValidFrom ValidTo
----------- -------------- ----------- --------------------------- ---------------------------
27 OwnerUserID 55 2017-06-21 00:19:35.0000000 2017-06-21 00:22:22.0000000
27 StatusID 3 2017-06-21 00:19:16.0000000 2017-06-21 00:19:35.0000000
27 StatusID 2 2017-06-21 00:22:22.0000000 2017-06-21 00:26:44.0000000
27 TradeDate 2017-01-28 2017-06-21 00:19:16.0000000 2017-06-21 00:19:35.0000000
Результаты показывают неправильный диапазон для (StatusID = 2). Дата ValidFrom должна быть 2017-06-21 00:19:35. Ошибка возникает из-за запроса, извлекающего ValidFrom из той же строки, что и ValidTo.
Вот мое усовершенствование проницательного начала Мартина. Он работает с использованием только ValidFrom. Он сообщает, когда каждое значение началось. Нам действительно не нужно показывать ValidTo, потому что это просто ValidFrom следующей строки.
USE tempdb
;
DROP TABLE IF EXISTS KrisisShifts_ShiftTrade
;
CREATE TABLE KrisisShifts_ShiftTrade
(
[ValidFrom] DATETIME2,
[ValidTo] DATETIME2,
[ShiftId] INT,
[TradeDate] DATE,
[StatusID] INT,
[LastActionDate] DATETIME2,
[OwnerUserID] INT,
[WorkerUserID] INT,
[WorkerEmail] VARCHAR(16),
[Archived] INT
);
INSERT INTO KrisisShifts_ShiftTrade
([ValidFrom], [ValidTo], [ShiftId], [TradeDate], [StatusID], [LastActionDate], [OwnerUserID],[WorkerUserID],[WorkerEmail], [Archived])
VALUES
('2017-06-21 00:26:44', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1)
;
WITH T
AS (SELECT ValidFrom,
ShiftId,
TradeDate,
StatusID,
LastActionDate,
OwnerUserID,
WorkerUserID,
WorkerEmail,
Archived,
nextTradeDate = LAG(TradeDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextStatusID = LAG(StatusID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextLastActionDate = LAG(LastActionDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextOwnerUserID = LAG(OwnerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextWorkerUserID = LAG(WorkerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextWorkerEmail = LAG(WorkerEmail) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
nextArchived = LAG(Archived) OVER (PARTITION BY ShiftId ORDER BY ValidFrom)
FROM KrisisShifts_ShiftTrade)
SELECT ShiftId,
Colname AS [Column],
value,
ValidFrom
FROM T
CROSS APPLY ( VALUES
('TradeDate', CAST(TradeDate AS NVARCHAR(4000)), CAST(nextTradeDate AS NVARCHAR(4000))),
('StatusID', CAST(StatusID AS NVARCHAR(4000)), CAST(nextStatusID AS NVARCHAR(4000))),
('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000)), CAST(nextLastActionDate AS NVARCHAR(4000))),
('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000)), CAST(nextOwnerUserID AS NVARCHAR(4000))),
('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000)), CAST(nextWorkerUserID AS NVARCHAR(4000))),
('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000)), CAST(nextWorkerEmail AS NVARCHAR(4000))),
('Archived', CAST(Archived AS NVARCHAR(4000)), CAST(nextArchived AS NVARCHAR(4000)))
) CA(Colname, value, nextvalue)
WHERE EXISTS(SELECT value
EXCEPT
SELECT nextvalue)
ORDER BY ShiftId,
[Column],
ValidFrom
;
Сюда входят начальные и текущие значения (лучше или хуже). Каждый столбец имеет одну строку, показывающую одинаковое начальное значение ValidFrom - 2017-06-21 00:19:16, а последняя строка для каждого столбца показывает текущее значение.
ShiftId Column value ValidFrom
----------- -------------- -------------------- -------------------
27 Archived 1 2017-06-21 00:19:16
27 LastActionDate 2017-01-09 16:23:39 2017-06-21 00:19:16
27 OwnerUserID 55 2017-06-21 00:19:16
27 OwnerUserID 45 2017-06-21 00:22:22
27 StatusID 3 2017-06-21 00:19:16
27 StatusID 2 2017-06-21 00:19:35
27 StatusID 3 2017-06-21 00:26:44
27 TradeDate 2017-01-28 2017-06-21 00:19:16
27 TradeDate 2017-01-27 2017-06-21 00:19:35
27 WorkerEmail test@hotmail.com 2017-06-21 00:19:16
27 WorkerUserID 34 2017-06-21 00:19:16
Однако важно отметить, что он правильно показывает, что (StatusID = 2) началось в 2017-06-21 00:19:35 и было заменено (StatusID = 3) в 2017-06-21 00:26:44. Если вам действительно нужно увидеть столбцы ValidFrom и ValidTo, вы можете заключить последний запрос, приведенный выше, в CTE и запросить его с помощью функции LEAD с '9999-12-31 23:59:59.99' для параметра «по умолчанию».
Изменить : я только что понял, что мое решение и решение Мартина неправильно обрабатывают случай, когда основная строка таблицы удаляется, а затем повторно вставляется позже. Приведенные ниже тестовые данные представляют собой случай, когда (ShiftId = 27) был удален 22.07.2017 00:26:55 и повторно вставлен позже 23.08.2017 00:26:59. Таким образом, (StatusID = 3) не существовало между 22.07.2017 00:26:55 и 23.08.2017 00:26:59. Правильное решение для этого потребует столбца ValidFrom и ValidTo, чтобы у нас могла быть строка для каждого столбца с ValidTo = 2017-07-22 00:26:55, соответствующая другой строке для того же столбца с ValidFrom = 2017-08-23 00:26:59, чтобы мы могли видеть диапазон, в котором не было данных.
VALUES
('2017-08-23 00:26:59', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:26:44', '2017-07-22 00:26:55', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1),
('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, 'test@hotmail.com', 1)
- Очень интересный вопрос.
- Подумайте о желаемом результате - столбец "Значение" должен содержать значения разных типов (int, decimal, date, binary, varchar, ...). Поэтому вам нужно конвертировать значения в varchar, или использовать sqlvariant, или двоичный. Затем в какой-то момент вам нужно будет распознать тип значения и обработать его по-разному для разных строк.
- Чтобы получить значения, вы можете попробовать использовать UNPIVOT:
SELECT someRowID, ValidTo, ValidFrom, col, val
FROM
(SELECT someRowID, ValidTo, ValidFrom /*, ... */,
[TradeDate], [StatusID], [LastActionDate], [AllowedRankID], [OwnerUserID], [OwnerEmail], [OwnerLocationID], [OwnerRankID], [OwnerEmployeeID], [WorkerUserID], [WorkerEmail], [WorkerLocationID], [WorkerRankID], [WorkerPlatoonID], [WorkerEmployeeID], [IsPartialShift], [Detail], [LastModifiedByUserID], [Archived], [UpdatedDate]
FROM ... ) AS p
UNPIVOT
(val FOR col IN ([TradeDate], [StatusID], [LastActionDate], [AllowedRankID], [OwnerUserID], [OwnerEmail], [OwnerLocationID], [OwnerRankID], [OwnerEmployeeID], [WorkerUserID], [WorkerEmail], [WorkerLocationID], [WorkerRankID], [WorkerPlatoonID], [WorkerEmployeeID], [IsPartialShift], [Detail], [LastModifiedByUserID], [Archived], [UpdatedDate])
) AS unpvt
Тогда аналогично UNPIVOT предыдущих значений
... и объединить результаты как
SELECT ...
FROM prevVals
INNER JOIN vals
ON vals.someRowID = prevVals.someRowID
AND vals.col = prevVals.col
WHERE vals.val <> prevVals.val -- yes, I know here can be a problem (NULLs, types)
Это всего лишь идея, и я надеюсь, что это поможет
Старайтесь не использовать функцию временных таблиц:). Попробуйте триггер, чтобы проверить изменения - это намного проще и намного короче.
Создайте изображение своей таблицы с отметкой времени и столбцом типа dml (row_id, s__dml_dt, s__dml_type + все ваши столбцы из исходной таблицы) для всех типов dml (i, u, d).
create trigger dbo.KrisisShifts_ShiftTrade on dbo.KrisisShifts_ShiftTrade
after insert as
begin
insert into dbo.KrisisShifts_ShiftTrade_logtable
select getdate() s__dml_dt, 'i' s__dml_type, * from inserted
-- for udpate select getdate() s__dml_dt, 'i' s__dml_type, * from inserted
-- for delete select getdate() s__dml_dt, 'd' s__dml_type, * from deleted
end
Теперь после вставки / удаления / обновления вы можете проверить все ваши исторические значения. Если вы хотите получить сводный результат, вы можете легко создать представление с помощью pivot для dbo.KrisisShifts_ShiftTrade_logtable.
Скрипт для регистрации всех таблиц в базе данных (он будет создавать таблицы с префиксом r_).
declare @table sysname
declare @nl varchar(2)
declare @create_table int
declare @cmd varchar(max)
declare @trgname sysname
declare c_tables cursor for
select table_name,
case
when exists (
select 2
from information_schema.tables
where table_name = 'r_'+ot.table_name
) then 0
else 1
end create_table
from information_schema.tables ot
where table_type = 'BASE TABLE'
and table_name not like 'r[_]%'
--and table_name like @tblfilter
open c_tables
fetch next from c_tables into @table,@create_table
while @@fetch_status=0
begin
-- logovaci tabulka
if @create_table=1
begin
set @cmd = 'create table r_'+@table+'(s__row_id int not null identity(1,1),s__dml_dt datetime not null,s__dml_type char(1) not null'
select @cmd = @cmd + char(13)+char(10)+','+column_name+' '+data_type+isnull('('+case when character_maximum_length<0 then 'max' else cast(character_maximum_length as varchar) end+')','')+' null' from information_schema.columns where table_name=@table order by ordinal_position
set @cmd = @cmd + ')'
exec(@cmd)
exec('create index i_s__dml_dt on r_'+@table+' (s__dml_dt)')
end
-- delete trigger
set @trgname = 'trg_'+@table+'_dl_del'
if object_id(@trgname) is not null exec('drop trigger '+@trgname)
exec('
create trigger '+@trgname+' on '+@table+' after delete as
begin
insert into r_'+@table+' select getdate(),''d'',t.* from deleted t
end
')
-- insert trigger
set @trgname = 'trg_'+@table+'_dl_ins'
if object_id(@trgname) is not null exec('drop trigger '+@trgname)
exec('
create trigger '+@trgname+' on '+@table+' after insert as
begin
insert into r_'+@table+' select getdate(),''i'',t.* from inserted t
end
')
-- update trigger
set @trgname = 'trg_'+@table+'_dl_upd'
if object_id(@trgname) is not null exec('drop trigger '+@trgname)
exec('
create trigger '+@trgname+' on '+@table+' after update as
begin
insert into r_'+@table+' select getdate(),''u'',t.* from deleted t
end
')
fetch next from c_tables into @table,@create_table
end
close c_tables
deallocate c_tables
Сколько места для хранения у вас есть?
В последний раз, когда я делал что-то подобное, мы вставляли новые строки для каждого измененного столбца в отдельную таблицу журнала изменений. Мы сделали это, используя логику на стороне клиента, но вы можете получить тот же эффект с помощью триггера.
Это занимает много места и замедляет ваши записи, но дает быстрый доступ к журналу изменений.
PS У нас не было общего решения, поэтому мы сделали это только для одной таблицы, которая нуждалась в поддержке пользовательского интерфейса. Во всем остальном используются псевдо-временные таблицы. (Старая версия SQL Server.)