SQL History Table Help

Posted on 2008-11-14
Last Modified: 2012-05-05
I need a bit of help developing a method to arcive records to a history table (SQL/VB.NET). I currently have a patients table linked to a patient detail table by the subjectid field. The patient detail table will be cleared and made ready for new data on an annual basis.

I need to be able to copy records from the patient detail table to the history table and the history table contain all years detail records for the patient so you may view the patients long term history.

I have created a duplicate history table, but not sure of the best way to handle inserting and updating changes to the patient detail table into the history table.

Any ideas would be appreciated.
Question by:wsenter
    LVL 4

    Expert Comment

    A simple approach is to create two queries:  one to append the details to be archived, and one to delete the details once they have been archived.  Use the query builder to build a query that will append data that is in the current details table to the archived details table.  You may want to include a date criterion in this query so that only details that occurred before the specified date are appended.  This can be tested safely because you will not be deleting any data.  Build another query, again with a matching, date criterion, that deletes the archived detail records from the current table.  By executing these two queries in succession, you will have accomplished your archive.

    You may take this a step further by creating a form with code behind that requests the date and runs both queries.
    LVL 1

    Accepted Solution

    That is not what I was looking for.  I have attached code so far. This works ok. It will read excel sheet and write to table. What I need it to do is read the first row of data from the spreadsheet and then compare the CustID field to records in the SQL table. If the record already exists, skip it. Otherwise write it to the SQL table.

    Option Explicit On
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    Imports DevExpress.XtraEditors
    Imports DevExpress.XtraPrinting
    Imports DevExpress.XtraPrinting.Native
    Imports Excel
    Public Class DBMigration
        Private intRowsAffected As Integer
        Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click
        End Sub
        Private Sub TransferExcel()
            Dim X As Integer
            Dim conn As System.Data.SqlClient.SqlConnection
            conn = New System.Data.SqlClient.SqlConnection("Data Source=,1433;Initial Catalog=C:\GITR\GIREPOSITORY\GIREPOSITORY.MDF;User ID=xxxxxxxx;Password=xxxxxxx")
            Dim ExcelFile As String
            ExcelFile = txtExcel.Text.Trim
            Dim cmd As System.Data.SqlClient.SqlCommand
            cmd = New System.Data.SqlClient.SqlCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "INSERT INTO [Test] SELECT Top 100 * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=" + ExcelFile + "', 'Select * from [Sheet1$]') "
            cmd.Connection = conn
            intRowsAffected = cmd.ExecuteNonQuery()
            ProgressBarControl1.Properties.Maximum = intRowsAffected
            For X = 1 To intRowsAffected
            MessageBox.Show("Transfer Complete", "Excel Import", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Sub
        Private Sub btnGroups_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGroups.Click
            ' Update the text box folder if the user clicks OK 
            If (OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
                txtExcel.Text = OpenFileDialog1.FileName
            End If
        End Sub
        Private Sub CheckFileBox()
            If txtExcel.Text = "" Then
                MessageBox.Show("You must enter a spreadsheet to import in the text field", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            End If
        End Sub
        Private Sub DBMigration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' Initializing progress bar properties
            ProgressBarControl1.Properties.Step = 1
            ProgressBarControl1.Properties.PercentView = True
            ProgressBarControl1.Properties.Minimum = 0
        End Sub
        Private Sub btnCloseConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseConnection.Click
        End Sub
    End Class

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now