Дизайн базы данных для исторической таблицы
У меня есть таблица работодателей (Таблица А). Я хочу создать аналогичную таблицу с дополнительным столбцом даты (таблица B), показывающим дату вставки строк. Я хочу вставлять строки из таблицы A в новую таблицу в конце каждого месяца. Есть ли способ автоматизировать это в SQL?
Я слышал, что вы можете сделать это с помощью хранимых процедур, но у меня нет опыта их использования, и я буду очень признателен за любые рекомендации.
Вот как может выглядеть таблица:
MemberID First Last Department Salary Daily_Hours Active_Flag
100 Evan Turner Sales 75000 8 1
200 Ron Gold Marketing 80000 9 1
300 Bob Gun HR 50000 8 1
Желаемый результат:
ID MemberID First Last Department Salary Daily_Hours Active_Flag Date
1 100 Evan Turner Sales 75000 8 1 3/31/19
2 200 Ron Gold Marketing 80000 9 1 3/31/19
3 300 Bob Gun HR 50000 8 1 3/31/19
4 100 Evan Turner Sales 75000 8 1 4/30/19
5 200 Ron Gold Marketing 80000 9 1 4/30/19
6 300 Bob Gun HR 50000 8 1 4/30/19
7 100 Evan Turner Finance 95000 8 1 5/31/19
8 200 Ron Gold Marketing 80000 9 1 5/31/19
9 300 Bob Gun HR 60000 8 1 5/31/19
Обратите внимание, как Эван Тернер сменил отдел и получил повышение зарплаты в мае месяце. Боб также получил прибавку к зарплате в том же месяце.
Идея состоит в том, что таблица A обновляется один раз в месяц, и я хочу записать это изменение в новую таблицу.
С точки зрения дизайна это не лучшее решение. Я бы посоветовал вам использовать дату VALID_FROM и VALID_TO и добавлять новую строку (и обновлять предыдущую) только в случае изменения. Затем вы можете настроить триггер на базовой таблице для автоматического выполнения работы (планирование не требуется).
Похоже, вы хотите использовать темпоральные таблицы.
Ответы 2
Вы можете создать процедуру, как показано ниже, и запланировать ее каждый конец месяца.
CREATE PROCEDURE usp_InsertHistory
AS
BEGIN
IF CAST(GETDATE() AS DATE) = CAST(EOMONTH(GETDATE()) AS DATE)
BEGIN
INSERT INTO HistoryTable
(
Id
,MemberId
,First
,Last
,Department
,Salary
,Daily_Hours
,Active_Flag
,Date
)
SELECT Id
,MemberId
,First
,Last
,Department
,Salary
,Daily_Hours
,Active_Flag
,GETDATE()
FROM Table
END
END
ТРИГГЕР был бы намного лучше! (имеют большую добавленную стоимость)
да. Но первоначальные ожидания состоят в том, чтобы сохранять полный набор данных в конце каждого месяца. Я бы более чем согласился на триггер в случае только отслеживания изменений, а не полного набора
Спасибо @Dheerendra. Это то, чего я хочу. Изначально я начал с триггера для записи изменений, но если кто-то из моей команды ошибется и сделает два обновления в одних и тех же строках, то мы можем получить две записи за один и тот же месяц, а это не то, что мне нужно. Меня интересует только полный набор изменений
убедившись, что у вас есть все изменения (через ТРИГГЕР), вы также получите полный набор в конце месяца.
@Luuk Ты прав. Дело в том, что мне нужна только одна запись для каждого работодателя каждый месяц, потому что мне нужна эта таблица, чтобы получать другую статистику из других таблиц. Наличие нескольких записей для одного и того же работодателя в одном и том же месяце добавило бы ненужных дубликатов.
Ваша таблица истории будет расти «быстро» со строками, которые также существуют в вашей таблице работодателей. Триггер также может сходить с ума, чтобы обновить запись, если она уже существует в течение определенного месяца, оставив вам максимум 1 запись на сотрудника в месяц и 0 записей для сотрудников без мутаций в этом месяце.
Добавьте новый столбец, который записывает, когда вставляется строка:
alter table t add column createdAt datetime default getdate();
Это будет автоматически заполнено датой вставки (продвижение вперед).
Вы спрашиваете, как написать
insertили как запланировать такое?