Solved

SQL Copy Record and Children and change ID references

Posted on 2009-07-14
3
595 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:darek1211
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 24849544
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
 

Author Comment

by:darek1211
ID: 24850106
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
 

Accepted Solution

by:
darek1211 earned 0 total points
ID: 24859537
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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