Начальные данные со старыми датами во временной таблице - SQL Server
Мне нужно заполнить данные для моей локальной цели разработки в следующей временной таблице, дата начала должна быть старой. Данная схема таблицы
CREATE TABLE [dbo].[Contact](
[ContactID] [uniqueidentifier] NOT NULL,
[ContactNumber] [nvarchar](50) NOT NULL,
[SequenceID] [int] IDENTITY(1,1) NOT NULL,
[SysStartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY NONCLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ContactHistory] , DATA_CONSISTENCY_CHECK = ON )
)
Мне нужно вставить некоторые старые датированные данные в эту таблицу.
INSERT INTO dbo.Contact
(
ContactID,
ContactNumber,
--SequenceID - this column value is auto-generated
SysStartTime,
SysEndTime
)
VALUES
(
NEWID(), -- ContactID - uniqueidentifier
N'9999912345', -- ContactNumber - nvarchar
-- SequenceID - int
'2017-09-01 06:26:59', -- SysStartTime - datetime2
NULL -- SysEndTime - datetime2
)
Я получаю следующую ошибку.
Невозможно вставить явное значение в столбец GENERATED ALWAYS в таблице 'DevDB.dbo.Contact'. Используйте INSERT со списком столбцов, чтобы исключить столбец GENERATED ALWAYS, или вставьте DEFAULT в столбец GENERATED ALWAYS.
Пожалуйста, помогите мне, как добавить или обновить старые данные в эту временную таблицу
5 ответов
Наконец я нашел решение
Шаг № 1: Нужно выключить его SYSTEM_VERSIONING
ALTER TABLE dbo.Contact SET (SYSTEM_VERSIONING = OFF);
Шаг № 2: нужно отбросить PERIOD FOR SYSTEM_TIME
ALTER TABLE dbo.Contact DROP PERIOD FOR SYSTEM_TIME
Шаг № 3: Вставить необходимую запись с прошлой датой
INSERT INTO dbo.Contact
(
ContactID,
ContactNumber,
SysStartTime,
SysEndTime
)
VALUES
(
NEWID(), -- ContactID - uniqueidentifier
N'1234567890', -- ContactNumber - nvarchar
'2014-09-13 00:00:00', -- SysStartTime - datetime2
'9999-12-31 23:59:59' -- SysEndTime - datetime2
)
Шаг № 4: нужно добавить PERIOD FOR SYSTEM_TIME
ALTER TABLE dbo.Contact
ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
Шаг № 5: Нужно включить его SYSTEM_VERSIONING
ALTER TABLE dbo.[Contact] SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE=dbo.[ContactHistory],DATA_CONSISTENCY_CHECK=ON)
);
Это оно...
Это можно сделать
Можно инициализировать ваши временные таблицы SQL (с системной версией) и историю с существующими данными, включая даты. Это просто включает в себя прыжки через кучу глупых обручей. Будем надеяться, что Microsoft предоставит нам лучший способ инициализировать эти таблицы с существующими историческими данными в будущем.
Краткая версия трюка по размещению существующих данных в этих таблицах с конкретными датами SYSTEM_TIME заключается в следующем:
- Добавьте версию системы в таблицу.
- Соберите данные для добавления в системную таблицу версий со столбцами, которые будут использоваться для заполнения столбцов SYSTEM_TIME в целевых таблицах. Обратите внимание, что значения SYSTEM_TIME будут в часовом поясе UTC, поэтому может потребоваться сделать что-то вроде AT TIME ZONE 'UTC', чтобы получить правильные даты.
- Отключите управление версиями системы.
- Создайте динамический SQL для операций CRUD.
- Включите управление версиями системы.
Вот упрощенный пример процесса:
-- System versioned table: dbo.ManagerList
-- Primary Key: ManagerName
-- History table: dbo.ManagerList_History
-- SYSTEM_TIME columns: _PeriodStart, _PeriodEnd
-- Table with Data to Import: #SourceData
DECLARE @Script varchar(max)
-- Disable system versioning
ALTER TABLE dbo.ManagerList SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.ManagerList
DROP PERIOD FOR SYSTEM_TIME;
-- Prepare source data (in temporary table #SourceData)
UPDATE A
SET _PeriodEnd = B.PeriodEnd
FROM #SourceData as A
INNER JOIN
(
SELECT ManagerName,
_PeriodStart,
_PeriodEnd =
LEAD(_PeriodStart, 1, datetime2fromparts(9999,12,31,23,59,59,9999999,7)) OVER
(
PARTITION BY ManagerName
ORDER BY _PeriodStart
)
) as B
ON A.ManagerName = B.ManagerName
AND A._PeriodStart = B._PeriodStart
-- DELETE from System-Versioned table
DELETE A
FROM dbo.ManagerList as A
WHERE NOT EXISTS
(
SELECT 1
FROM #SourceData
WHERE ManagerName = A.ManagerName
)
-- UPDATE script for System-Versioned table
SET @Script =
'UPDATE A ' +
'SET FavoriteColor = B.FavoriteColor, ' +
' _PeriodStart = B._PeriodStart ' +
'FROM dbo.ManagerList as A ' +
' INNER JOIN #SourceData as B ' +
' ON A.ManagerName = B.ManagerName ' +
'WHERE B._PeriodEnd > datefromparts(9999,12,31) ' +
' ( ' +
' A.ManagerName != B.ManagerName ' +
' OR A._PeriodStart != B.PeriodStart ' +
' )'
EXEC (@Script)
-- UPDATE script for System-Versioned table
SET @Script =
'UPDATE A ' +
'SET FavoriteColor = B.FavoriteColor, ' +
' _PeriodStart = B._PeriodStart ' +
'FROM dbo.ManagerList ' +
' INNER JOIN #SourceData as B ' +
' ON A.ManagerName = B.ManagerName ' +
'WHERE B._PeriodEnd > datefromparts(9999,12,31) ' +
' AND (' +
' A.FavoriteColor != B.FavoriteColor ' +
' OR A._PeriodStart != B.PeriodStart ' +
' )'
EXEC (@Script)
-- INSERT script for System-Versioned table
SET @Script =
'INSERT dbo.ManagerList ' +
'( ' +
' ManagerName, ' +
' FavoriteColor, ' +
' _PeriodStart ' +
') ' +
'SELECT ManagerName, ' +
' FavoriteColor, ' +
' _PeriodStart ' +
'FROM #SourceData as A ' +
'WHERE _PeriodEnd > datefromparts(9999,12,31) ' +
' AND NOT EXISTS ' +
' ( ' +
' SELECT 1 ' +
' FROM dbo.ManagerList ' +
' WHERE ManagerName = A.ManagerName ' +
' )'
EXEC (@Script)
-- DELETE script for History table
SET @Script =
'DELETE A ' +
'FROM dbo.ManagerList_History as A ' +
'WHERE NOT EXISTS ' +
' ( ' +
' SELECT 1 ' +
' FROM #SourceData ' +
' WHERE ManagerName = A.ManagerName ' +
' AND _PeriodEnd < datefromparts(9999,12,31) ' +
' )'
EXEC (@Script)
-- UPDATE script for History table
SET @Script =
'UPDATE A ' +
'SET FavoriteColor = B.FavoriteColor ' +
'FROM dbo.ManagerList_History as A' +
' INNER JOIN #SourceData as B ' +
' ON A.ManagerName = B.ManagerName ' +
' AND A._PeriodStart = B._PeriodStart ' +
'WHERE B._PeriodEnd < datefromparts(9999,12,31) ' +
' AND (' +
' A.FavoriteColor != B.FavoriteColor' +
' )'
EXEC (@Script)
-- INSERT script for History table
SET @Script =
'INSERT dbo.ManagerList ' +
'( ' +
' ManagerName, ' +
' FavoriteColor, ' +
' _PeriodStart ' +
') ' +
'SELECT ManagerName, ' +
' FavoriteColor, ' +
' _PeriodStart ' +
'FROM #SourceData as A ' +
'WHERE _PeriodEnd < datefromparts(9999,12,31) ' +
' AND NOT EXISTS ' +
' ( ' +
' SELECT 1 ' +
' FROM dbo.ManagerList_History ' +
' WHERE ManagerName = A.ManagerName ' +
' AND _PeriodStart = A._PeriodStart ' +
' )'
EXEC (@Script)
-- Re-enabling system versioning
-- Note: Making this dynamic SQL solves compiler error
SET @Script =
'ALTER TABLE dbo.ManagerList ' +
'ADD PERIOD FOR SYSTEM_TIME (_PeriodStart, _PeriodEnd)';
EXEC (@Script)
ALTER TABLE dbo.ManagerList
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ManagerList_History));
Основная проблема, по-видимому, заключается в том, что компилятор не всегда распознает, когда что-то отключено в системных версионных таблицах, и не позволяет компилировать или запускать нединамический SQL. Не знаю, почему это можно сделать с помощью динамического SQL, но я очень рад, что это возможно.
После того, как таблицы изначально заполнены из существующих, все ограничения на изменение дат SYSTEM_TIME или удаление записей в истории имеют смысл.
Надеюсь, это кому-нибудь поможет.
Чего ты пытаешься достичь? Столбцы GENERATED ALWAYS - это технические столбцы, и если вы установите их так, что вы не сможете их обновить, они будут обновлены автоматически. Для отслеживания изменений у вас есть таблица ContactHistory.
Обычно вы должны использовать следующие вставки:
INSERT INTO dbo.Contact
(
ContactID, --NEWID()
ContactNumber
)
VALUES
(
'045ABA61-1C64-4FE4-B079-18A9A50335D5', -- ContactID - uniqueidentifier
N'9999912345'
);
Затем после первой вставки вот что у вас есть:
select * from dbo.Contact
select * from dbo.ContactHistory
У вас еще нет записей в истории, так как она не хранит фактическую запись. Теперь, если вы хотите изменить данные, то вы используете обычные операторы UPDATE, игнорируя эти столбцы GENERATED ALWAYS:
UPDATE dbo.Contact SET ContactNumber = '123456789' WHERE ContactId = '045ABA61-1C64-4FE4-B079-18A9A50335D5'
Давайте проверим данные еще раз:
select * from dbo.Contact
select * from dbo.ContactHistory
Теперь у вас немного другая ситуация:
Как вы можете видеть, фактические данные обновляются, как и ожидалось, и таблица истории имеет старую запись с "закрытым" EndTime.
Так что, если вы хотите иметь встроенную поддержку SCD, тогда Sql Server сделает все, только не трогайте эти столбцы. Если по каким-то особым причинам вам необходимо обновить эти столбцы, просто не используйте столбцы GENERATED ALWAYS и используйте для этого ограничения DEFAULT.
Примечание SQL Server 2016 представлен только System-Versioned Temporal Tables из стандарта ANSI 2011, который определяет 3 типа временных таблиц
По состоянию на декабрь 2011 года ИСО / МЭК 9075, Язык базы данных SQL:2011 Часть 2: SQL/Foundation включил в определения таблиц предложения для определения "таблиц периодов времени приложения" (действительные таблицы времени), "таблиц с системной версией" (время транзакции). таблицы) и "системные периодические таблицы времени приложения" (битемпоральные таблицы). https://en.wikipedia.org/wiki/Temporal_database
Системные версии временных таблиц не поддерживают изменения в уже зарегистрированной истории. Таким образом, чтобы создать тестовый набор временных данных, вам нужно повторно выполнить все исторические INSERT в порядке.
Вы не можете вставить старую временную метку данных во временную таблицу напрямую. Ценности SysStartDate а также SysEndDate обновляются автоматически при работе с таблицей. Одним из возможных решений проблемы является вставка тестовых данных, как вы делаете:
INSERT INTO dbo.Contact
(
ContactID,
ContactNumber,
)
VALUES
(
NEWID(), -- ContactID - uniqueidentifier
N'9999912345', -- ContactNumber - nvarchar
)
а затем итеративно обрабатывать UPDATES
DECLARE @i int = 0 -- counter
WHILE @i < 30
BEGIN
WAITFOR DELAY '00:00:10' -- hh:mi:ss, wait for 10 seconds
-- perform update with random data
SET @i = @i + 1
END