SQL History Table Help

Posted on 2008-11-14
Medium Priority
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

Expert Comment

ID: 22964547
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.

Accepted Solution

wsenter earned 0 total points
ID: 22985717
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

749 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