Post reply

Cascading Copy

  • April 25, 2005 at 9:38 pm

    #163375

    I have a table Project, that has a ProjID key, and for each project there are several nodes that have the ProjID foreign key. I need to make a copy of a project with associated nodes and place this copy back into the database. This project and nodes need to be renamed. My question is, how do I rename nodes within a stored procedure without passing the new names to the stored procedure?

    Basically the number of nodes vary, so I can't pass a given number of names to rename all the nodes.

    Hope this makes sense,

    thanks.

  • April 27, 2005 at 4:49 am

    #555148

    Are the number of nodes unlimited or is there a ceiling ?! Also, is there a naming convention for each of these nodes (node1, node 2 etc..) - could you post some data samples as well as an example of the output you're trying to achieve ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 27, 2005 at 6:50 am

    #555191

    So are you changing the ProjID key? What gets changed in the nodes? This could be a process similar to Type 2 changes in a datawarehouse.

    Before being more constructive we'd have to see some sample data of before and after.

     

    --------------------
    Colt 45 - the original point and click interface

  • April 27, 2005 at 8:54 am

    #555263

    As a simple example: say you have these two tables,

    Project:

    ProjID (key),

    Location,

    Time

    Node:

    NodeID (key),

    ProjID (foriegn key)

    You can have multiple nodes associated with a single project. I need to make a copy of the project and all nodes. So, the ProjID and all nodes need to be copied, renamed, and inserted back into the tables.

    I guess the question is how to automatically rename using a stored procedure. That is, not passing the new name as an argument.

    Hopes this clears up my situation.

  • April 27, 2005 at 4:38 pm

    #555409

    Ok, so you've got a project record that's like this

    1, West, 14:00

    Node record is like

    1, 1

    I'll presume you have the ProjID and NodeID set as IDENTITY fields. So the statements in your stored procedure would run something like

    DECLARE @NewID int
INSERT INTO Project (Location, Time)
SELECT <New Location>, <New Time> FROM Project WHERE ProjID = 1
SET @NewID = SCOPE_IDENTITY()
INSERT INTO Node (ProjID)
SELECT @NewID as ProjID FROM Node WHERE ProjID = 1

     

    --------------------
    Colt 45 - the original point and click interface

  • May 5, 2005 at 3:25 pm

    #557234

    Thanks,

    Dave

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply