SQL Copy Record and Children and change ID references

I need help with the following;

Using MSSQL and VS2008 I'm trying to copy 3 tables with ID references to each other.  Table1 is called GOAL, table2 is called ACTIVITY, table3 is called PROGRESS.  Goal is the first and has an autoincrementing PK.  Activity is second and also has an autoincrementing PK, Progress is third and also has an autoincrementing PK.

There is one Goal, which can have several Activities, and each Activity can have several Progress (updates).  Each table hold a reference to the previous PK.  I.E.; Goal has a single PK, Activity has its own PK as well as the key of the Goal from which it stems, and the Progress also has its own PK, as well as the Key of the Activity and Goal it stems from.

Given the below statement in VS(VB in this case)

Dim sql As String = "INSERT INTO Goal (District, InternalOnly, Needs, SupportedBy, Focus, GoalArea, GoalDesc, Standard, ByWho, created, edited, Archived, FileCount)" & _
                    " SELECT District, InternalOnly, Needs, SupportedBy, Focus, GoalArea, GoalDesc, Standard, ByWho, created, edited, Archived, FileCount" & _
                    " FROM Goal WHERE GoalID = " & OLDGoalID & " Select scope_identity()"

                Dim cmd As New SqlCommand(sql, conn, t)
                NEWGoalID = cmd.ExecuteScalar()

I would like to then use the OLDGoalID and NEWGoalID variables to make a copy/duplicate of all Activies that have the OLDGoalID, let autoincrement handle the PK, and then replace the OLDGoalID in the record with the NEWGoalID from the initial Insert above.  Probably with another InsertInto/Select statement similar to above.

Then finally do the same for the Progress table, which holds references to the 'parent' ActivityID and that Activity's 'parent' GoalID, and update those 2 values with the new values from the 2 new records above...

Any help would be appreciated.

thanks.
GOAL table
===================================================
GoalID*             Misc1            Misc2.....
--------------------------------------------------
1                      abc               abc
2                      xyz               xyz
3                      www           zzz
===================================================
 
ACTIVITY table
===================================================
ActivityID*      GoalID             Misc1            Misc2.....
---------------------------------------------------------------
1                    1                      abc               abc
2                    1                      a2bc             a3bc
3                    1                      a4bc             a4bc
4                    2                      xyz               xyz
===================================================
 
PROGRESS table
===================================================
ProgressID*        ActivityID      GoalID             Misc1            Misc2.....
------------------------------------------------------------------------------
1                       1                    1                      abc               abc
2                       1                    2                      xyz               xyz
3                       1                    2                      abc               abc
4                       2                    3                      abc               abc
5                       2                    3                      abc               abc
6                       2                    1                      abc               abc
7                       4                    2                      abc               abc
 
 
* = autoincrementing PK

Open in new window

darek1211Asked:
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.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I do not understand the necessity for a functional dependency between GOAL and PROGRESS.

The following schema would help you achieve your requirements more easily...

GOAL: id
ACTIVITY: id
ACTIVITY_GOAL: goalid, activityid
ACTIVITY_PROGRESS: activityid, status, date

If there is a change in GOAL simply update ACTIVITY_GOAL...If there new PROGRESS on an ACTIVITY simply add a new line in ACTIVITY_PROGRESS...

HTH
0
darek1211Author Commented:
Racimo, hindsight's 20/20 :) unfortunately I'm not at a point any longer where I have the ability to modify the existing structure of the DB that much, as there's too much other code throughout the app that would not be happy.

I don't think the following will make any difference in your suggestion, but just so we're on the same page...I am needing to make a duplicate of a single GOAL record, and then subsequently duplicate all related ACTIVITY records, and each activity's related PROGRESS records, and then keep them related to each other.
0
darek1211Author Commented:
dirty solution that seems to work:
   Function CopyDBRecords(ByVal OLDGoalID As String) As Boolean
 
        Dim savedok As Boolean = False
        Dim NEWGoalID As String = ""
        Dim connOneTime As New SqlConnection(ConStr)
 
        Using conn As New SqlConnection(ConStr)
            conn.Open()
 
            'break it down and commit when everything went smoothly
            Dim t As SqlTransaction = conn.BeginTransaction()
            Dim sql As String = ""
            Try
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                'first copy the IO
                sql = "INSERT INTO Goal (District, InternalOnly, Needs, SupportedBy, Focus, GoalArea, GoalDesc, Standard, ByWho, created, edited, Archived, FileCount, SchoolYear)" & _
                    " SELECT District, InternalOnly, Needs, SupportedBy, Focus, GoalArea, GoalDesc, Standard, ByWho, created, edited, Archived, FileCount, '" & ddlSchoolYear2New.SelectedItem.Text & "'" & _
                    " FROM Goal WHERE GoalID = '" & OLDGoalID & "' Select scope_identity()"
 
                Dim cmd As New SqlCommand(sql, conn, t)
                NEWGoalID = cmd.ExecuteScalar()
                lblStatusInfo.Text += "</br>Old GoalID:" & OLDGoalID & "--New GoalID:" & NEWGoalID
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                'update the 'Original' keys
                sql = "UPDATE Activity SET OriginalActivityID = ActivityID, OriginalGoalID=GoalID"
                Dim cmdU1 As New SqlCommand(sql, connOneTime)
                connOneTime.Open()
                cmdU1.ExecuteNonQuery()
                connOneTime.Close()
                cmdU1.Dispose()
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                'copy all Activities tied to OLD GoalID to new Activies with new ActivityID
                sql = "INSERT INTO Activity (GoalID, StdActionID, Description, TargetDate, BeginDate, EndDate, Evidence, EvidenceChoices, Resources," & _
                    " LEAstaff, Criterion, Responsibles, ResponsiblesEMAILS, PriResponsible, PriResponsibleEMAIL, ADNotify, ADNotifyEMAIL, ByWho, created," & _
                    " FileCount, SchoolYear, OriginalActivityID, OriginalGoalID)" & _
                    " SELECT '" & NEWGoalID & "', StdActionID, Description, TargetDate, BeginDate, EndDate, Evidence, EvidenceChoices, Resources," & _
                    " LEAstaff, Criterion, Responsibles, ResponsiblesEMAILS, PriResponsible, PriResponsibleEMAIL, ADNotify, ADNotifyEMAIL, ByWho, '" & System.DateTime.Now & "'," & _
                    " FileCount, '" & ddlSchoolYear2New.SelectedItem.Text & "', OriginalActivityID, OriginalGoalID" & _
                    " FROM Activity WHERE GoalID = '" & OLDGoalID & "'"
                Dim cmdA As New SqlCommand(sql, conn, t)
                cmdA.ExecuteNonQuery()
                'lblStatusInfo.Text += "</br>A"
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                'update the 'Original' keys
                sql = "UPDATE Progress SET OriginalActivityID = ActivityID, OriginalGoalID=GoalID, OriginalProgressID=ProgressID"
                Dim cmdU2 As New SqlCommand(sql, connOneTime)
                connOneTime.Open()
                cmdU2.ExecuteNonQuery()
                connOneTime.Close()
                cmdU2.Dispose()
                '/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                'copy old progress update to new
                sql = "INSERT INTO Progress (GoalID, ActivityID, ProgDesc, EarlyInt, ByWho, created, ProgressState, FileCount," & _
                " OriginalActivityID, OriginalGoalID, OriginalProgressID)" & _
                " SELECT '" & NEWGoalID & "', Activity.ActivityID, ProgDesc, EarlyInt, Progress.ByWho, '" & System.DateTime.Now & "', ProgressState, Progress.FileCount," & _
                " Progress.OriginalActivityID, Progress.OriginalGoalID, OriginalProgressID" & _
                " FROM Progress INNER JOIN Activity ON Progress.OriginalActivityID = Activity.OriginalActivityID" & _
                " WHERE Activity.OriginalGoalID = '" & OLDGoalID & "' AND Activity.OriginalActivityID <> Activity.ActivityID"
 
                Dim cmdP As New SqlCommand(sql, conn, t)
                cmdP.ExecuteNonQuery()
                'lblStatusInfo.Text += "</br>PU"
 
                t.Commit()
                savedok = True
                conn.Close()
                cmd.Dispose()
                cmdA.Dispose()
                cmdP.Dispose()
            Catch ex As Exception
                lblStatusIOManagement.Text = "Commit Exception Type: " & ex.GetType().ToString() & "</br>"
                lblStatusIOManagement.Text += ex.Message
                ' Attempt to roll back the transaction.
                Try
                    t.Rollback()
                Catch ex2 As Exception
                    ' This catch block will handle any errors that may have occurred
                    ' on the server that would cause the rollback to fail, such as
                    ' a closed connection.
                    lblStatusIOManagement.Text += "Rollback Exception Type: " & ex2.GetType().ToString() & "</br>"
                    lblStatusIOManagement.Text += "Message: " & ex2.Message
                End Try
 
            End Try
        End Using
 
        Return savedok
 
    End Function

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

From novice to tech pro — start learning today.