Solved

SQL Copy Record and Children and change ID references

Posted on 2009-07-14
3
587 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
  • 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

10 Experts available now in Live!

Get 1:1 Help Now