How to create a History table from the given data in SQL? Please let me know the possible ways step by step

Question

Creating History Tables for the Database

The Database will need many history tables. Some of them will track a single data element and others will track multiple related fields. All history tables will contain TcId, ProgSolNbr, StartDate, EndDate, and the fields that are being tracked. History tables will have a foreign key relationship to the main table (usually Accounts) based on the TcId. The current record for each account will be identified by an EndDate of ‘9991231’. Any errors that occur during the process of updating history tables will be automatically tracked in the HistLog table. Below is a list of history tables that are needed and a sample of a stored procedure to update a history table.

HistExtStatInd

            ExtStatInd

HistIntStatInd

            IntStatInd

HistStatus

            Status

HistCycle

            Cycle

HistCreditBureauInd

            CreditBureauInd

HistPhone

            Phone   Phone2    Phone3

HistPricingStrategy

            PricingStrategy

HistBal

BalAmt BalPrinAmt BalFeeAmt BalTransferAmt BalMiscAmt BalDisputeAmt

HistNameAddress

            Name   Name2   Address   Address2   City   State   Zip   Zip4

[What have you tried](http://whathaveyoutried.com)? – HABO Sep 03 '12 at 18:26 — HABO, Sep 03 '12 at 18:26

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

If you're using SQL Server 2008, then use Change Data Capture

If you're using SQL Server 2005 then use triggers:

SQL Server history table - populate through SP or Trigger?

edited May 23 '17 at 12:20

Community

1
1

answered Sep 03 '12 at 18:25

Chris Gessler

22,727
7
57
83

How to create a History table from the given data in SQL? Please let me know the possible ways step by step

1 Answers1