CODEWITHSUNDEEP
sqlsql-server
AGuyHasNoName
AGuyHasNoName

Reputation: 109

Database Design For Historical Table

I have a table of employers(Table A). I want to create a similar table with an extra date column(Table B), showing the date the rows were inserted. I want to insert the rows from Table A to the new table at the end of every month. Is there a way to automate this in SQL?

I have heard that you can do this through stored procedures, but I don't have any experience using them and any guidance would be greatly appreciated.

Here is what the table may look like:

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

Desired output:

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

Note how Evan Turner has changed department and received a salary bump on the month of May. Bob got a salary increase in the same month as well

The idea is that table A gets updated once every month and I want to record that change into the new table.

Upvotes: 0

Views: 378

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Add a new column that records when a row is inserted:

alter table t add column createdAt datetime default getdate();

This will be automatically populated with the insertion date (moving forward).

Upvotes: 0

Dheerendra
Dheerendra

Reputation: 308

You can create a procedure like below and schedule it every month end.

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

Upvotes: 1

Related Questions