SQL History Table Help

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.
LVL 1
wsenterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OdeMonkeyCommented:
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.
0
wsenterAuthor Commented:
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.

Thanks.
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
        TransferExcel()
    End Sub
 
    Private Sub TransferExcel()
        Dim X As Integer
        CheckFileBox()
        Dim conn As System.Data.SqlClient.SqlConnection
        conn = New System.Data.SqlClient.SqlConnection("Data Source=10.10.1.30,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
        conn.Open()
        intRowsAffected = cmd.ExecuteNonQuery()
        ProgressBarControl1.Properties.Maximum = intRowsAffected
        For X = 1 To intRowsAffected
            ProgressBarControl1.PerformStep()
            ProgressBarControl1.Update()
        Next
        conn.Close()
        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)
            txtExcel.Focus()
        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
        Me.Close()
    End Sub
 
End Class

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.