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

Select allOpen in new window