Selectively duplicating a set of records, while maintaining parent/child hierarchy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selectively duplicating a set of records, while maintaining parent/child hierarchy

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-26 : 09:18:18

Phibian writes "I have an application that stores information about fixed assets (in this case, mainly computer related). I'd like to be able to "split" assets based on their quantity attribute, but no matter what angle I think about it - I end up with orphans or wrongly assigned parents. That's not very clear, but hopefully my example will make it clear.

Here's the database structure I'm working with (it would be difficult to change):

Table: Asset
---------------
ID
Parent_Asset_ID
AssetClass_ID
Value

Table: AssetClass
-----------------
ID
Name (examples include Fixed assets such as Workstation, Mouse etc as well as Attributes such as quantity and name)
AssetDataType_ID (if 0, it's an attribute)

There are only ever two levels of assets - so Parent Asset can have a subasset, but subassets cannot also be parents.

So, for example, say I had a Workstation, with a mouse subcomponent. Workstations and mice each have attributes (which are specific to their class).

Then my Asset table would be populated as follows:


ID   Parent_Asset_ID   AssetClass_ID   Value
1    0                   (Workstation)    
2    1                   (Quantity)        2
3    1                   (Mouse)
4    3                   (Quantity)        1

I'm trying to write a (preferably fast) SQL statement that will have the following result:


ID   Parent_Asset_ID   AssetClass_ID   Value
1    0                   (Workstation)    
2    1                   (Quantity)        1
3    1                   (Mouse)
4    3                   (Quantity)        1
5    0                   (Workstation)    
6    5                   (Quantity)        1
7    5                   (Mouse)
8    7                   (Quantity)        1

I'm not too bothered about updating the value of the quantity to be the "correct" value, because I can use an update to handle this.

I'm currently starting by duplicating the top parent separately. And I can then duplicate all of the original assets attributes and subassets and assign their parent IDs to the new duplicated asset. But then the attributes of my subassets are assigned to the parent (as opposed to the subasset).

So my code doesn't work and plus it is slower than watching grass grow.

In case you are interested, here's what I'm doing:
(Note ParentID is programmatically retrieved by an external program)

// Duplicate Parent
INSERT INTO [Asset] ([Parent_Asset_ID], [AssetClass_ID]) SELECT [Parent_Asset_ID], [AssetClass_ID] FROM Asset WHERE ID=ParentID
//Get new ID (NewParentID)
SELECT TOP 1 ID FROM Asset WHERE [Parent_Asset_ID]=(SELECT Parent_Asset_ID FROM Asset WHERE ID=ParentID) AND [AssetClass_ID]=(SELECT AssetClass_ID FROM Asset WHERE ID=ParentID)
//Duplicate subassets and attributes
INSERT INTO [Asset] ([Parent_Asset_ID], [AssetClass_ID], [Value]) SELECT NewParentID, [AssetClass_ID], [Value] FROM Asset WHERE Parent_Asset_ID IN (SELECT ID FROM Asset WHERE [Parent_Asset_ID]=ParentID)

Any help would be appreciated.

Mary"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 10:29:01

I have a question: are there 2 mice for this workstation, or 2 workstations with 1 mouse each? It's really not clear from your table design. If you keep having orphans, then no matter how hard it is, you need to think about modifying the design.

If you model your data according to the 2nd structure, you exacerbate another issue: recursion. Now if you want how many mice Workstation 1 has, you have to recurse down through all objects to get it. It's an issue for the 1st model too, but with fewer levels.

It might be better to keep the objects in one table, and their attributes in a separate table. That way you reduce the chance of orphaning something:

Objects Table
ID     Parent    Object
1      0         Workstation
2      1         Mouse
3      0         Workstation
4      3         Mouse
5      3         Mouse Pad

Attributes Table
ObjectID   Attribute     Value
1          Quantity      1
1          Owner         John Smith          
2          Quantity      1
2          Owner         John Smith
3          Quantity      1
...etc.

Phibian
Starting Member

32 Posts

Posted - 2002-03-27 : 12:46:17

It's not that I "keep" having orphans, it's that I don't know how to re-assign my parent IDs to the new, duplicated assets. I'd have the same problem with two tables.

Here's what I can do right now:
Table:
ID Parent_Asset_ID AssetClass_ID Value
1 0 (Workstation)
2 1 (Quantity) 3
3 1 (Mouse)
4 3 (Quantity) 1

Split 3 workstations (with one mouse) into 2 workstations (with one mouse) and 1 workstation (with one mouse).

ID Parent_Asset_ID AssetClass_ID Value
1 0 (Workstation)
2 1 (Quantity) 2
3 1 (Mouse)
4 3 (Quantity) 1
5 0 (Workstation)
6 5 (Quantity) 1
7 5 (Mouse)
8 3 (Quantity) 1

Note that for ID 8, the Parent_Asset_ID is incorrect. It is pointing to the original parent.

I could break it down further so that I create duplicates of subassets one at a time, and find the ID and then I could assign the new ID to the attributes. But at this point the number of SQL statements is getting out of control (~3 for every subasset, with an average of 10 subassets per asset...) Furthermore, the # of statements required would vary, and would have to be generated programmatically, at which point I might as well take a different approach.

Any ideas?

Here's my SQL
// Create duplicate of parent asset
INSERT INTO [Asset] ([Site_ID], [Parent_Asset_ID], [AssetClass_ID]) SELECT [Site_ID], [Parent_Asset_ID] FROM Asset WHERE ^Split^<>0 AND ID=ParentID

// Get the ID of the new parent
SELECT ID FROM Asset WHERE Site_ID=SiteID ORDER BY ID DESC">

// Create duplicate children (using a negative site to distinguish duplicates from originals)
INSERT INTO [Asset] ([Site_ID], [Parent_Asset_ID], [AssetClass_ID],[Value]) SELECT -[Site_ID], [Parent_Asset_ID], [AssetClass_ID], [Value] FROM Asset WHERE ^Split^<>0 AND (Parent_Asset_ID=ParentID OR Parent_Asset_ID IN (SELECT ID FROM Asset WHERE [Parent_Asset_ID]=ParentID))

// Assign duplicate children to new parent
UPDATE [Asset] SET [Parent_Asset_ID]=NewID WHERE Site_ID=-SiteID AND Parent_Asset_ID=ParentID

// Flip site sign
UPDATE [Asset] SET [Site_ID]=SiteID WHERE Site_ID=-SiteID

// Update quantity values
UPDATE [Asset] SET [Number]=(^Split^) WHERE ^Split^<>0 AND Parent_Asset_ID=^NewID^ AND AssetClass_ID=85
UPDATE [Asset] SET [Number]=(0^ValueQuantity^-^Split^) WHERE ^Split^<>0 AND Parent_Asset_ID=ParentID AND AssetClass_ID=85

--------------------------------------------------
Final note: While separating attributes and assets might be good from several perspectives, from other perspectives of the application (that I didn't get into) it would be much less convenient.

And anyway, unless there was some extremely significant and measurable benefit - I *can't* change the database structure... So I'd need a different solution.

Thanks!

Phibian
Starting Member

32 Posts

Posted - 2002-03-27 : 12:57:43

Rereading my second post I have some clarifications that may or may not help :)

-The table (and thus code) I've provided is simplified...
-^Split^ is the number entered by the user as the quantity for duplicates (so in the example, it's equal to 1)
-^ValueQuantity^ is the original quantity (so in the example, it's equal to 3)

Jay99

468 Posts

Posted - 2002-03-27 : 13:44:05

I'd love to help you here, but you need to clear up a few things:

1. What is Site_ID? Its in your DML but not your DDL.
2. What is ParentID? Again not in your DML.
3. Just so we are all clear, what language is this? '//' is not a valid commenting character in SQL Server, nor is '^blah^' a valid variable name.
4. What is a 'quantity of duplicates'?
5. Does your model represent some sort of reality? Is a mouse plugged into ComputerA somehow fundamentally different than the mouse plugged into ComputerB, while having 2 mice plugged into the same computer makes them fundamentally the same? In my world, if I take a mouse and unplug it from all computers, it still exists . . .
6.I don't understand what it means to "split" an asset. To me, that means taking an axe and chopping the monitor in half, but you are going after something different. . . .

Jay
<O>

Phibian
Starting Member

32 Posts

Posted - 2002-03-27 : 14:54:24

1. What is Site_ID? Its in your DML but not your DDL.
Sorry! I had chopped it out as part of my simplification but then added it back because I use it to distinguish between duplicates and originals.

Site_ID is a number that refers to the site that the asset belongs to.
I should have added the column to my example like so:

Original
ID Parent_Asset_ID AssetClass_ID Value Site_ID
1 0 (Workstation) 99
2 1 (Quantity) 2 99
3 1 (Mouse) 99
4 3 (Quantity) 1 99

Desired Result:
ID Parent_Asset_ID AssetClass_ID Value Site_ID
1 0 (Workstation) 99
2 1 (Quantity) 2 99
3 1 (Mouse) 99
4 3 (Quantity) 1 99
5 0 (Workstation) 99
6 5 (Quantity) 1 99
7 5 (Mouse) 99
8 7 (Quantity) 1 99

2. What is ParentID? Again not in your DML.
ParentID is a variable that is inserted into SQL code - in the example, I guess ParentID is equal to 1. NewID is equal to 5. SiteID is equal to 99 (from above).

3. Just so we are all clear, what language is this? '//' is not a valid commenting character in SQL Server, nor is '^blah^' a valid variable name.
It's not exactly a language - I use a program (CLE see www.phibian.com) that allows me to embed SQL calls into HTML. The comments were just for clarity - they aren't actually in my code. What gets passed to SQL Server is pure SQL. In retrospect, I should've written only what was passed as opposed to being lazy - sorry! I've rewritten my SQL example so that it's clearer (hopefully <g>)

Step 1: Create duplicate of parent asset
INSERT INTO [Asset] ([Site_ID], [Parent_Asset_ID], [AssetClass_ID]) SELECT [Site_ID], [Parent_Asset_ID] FROM Asset WHERE ID=1

Step 2: Get the ID of the new parent
SELECT ID FROM Asset WHERE Site_ID=99 ORDER BY ID DESC">

Step 3: Create duplicate children (using a negative site to distinguish duplicates from originals)
INSERT INTO [Asset] ([Site_ID], [Parent_Asset_ID], [AssetClass_ID],[Value]) SELECT -[Site_ID], [Parent_Asset_ID], [AssetClass_ID], [Value] FROM Asset WHERE (Parent_Asset_ID=1 OR Parent_Asset_ID IN (SELECT ID FROM Asset WHERE [Parent_Asset_ID]=1))

Step 4: Assign duplicate children to new parent
UPDATE [Asset] SET [Parent_Asset_ID]=5 WHERE Site_ID=-99 AND Parent_Asset_ID=1

Step 5: Flip site sign
UPDATE [Asset] SET [Site_ID]=99 WHERE Site_ID=-99

Step 6: Update quantity values
UPDATE [Asset] SET [Value]=1 WHERE Parent_Asset_ID=5 AND AssetClass_ID=(Quantity)
UPDATE [Asset] SET [Value]=(3-1) WHERE Parent_Asset_ID=1 AND AssetClass_ID=(Quantity)

Questions 4/5/6 are all basically the same question - I obviously didn't explain what I'm trying to do clearly enough - sorry! (please bear with me!)

The idea is that you can "group" assets.

So you only need to create 1 physical asset in order to refer to more than one. So in the example, we are creating one physical workstation asset, but because it has quantity three - in reality you can assume that there are actually three physical workstations.

The reason is because often we have a bunch of completely identical assets (such as a workstation) and we want to think (and retrieve information) about them as a group. However, occasionally, we want to split the "group" into more than one piece (for instance, if we purchase 5 workstations, but then later sell three) We've decided that it's enough to be able to split the asset group into two sections. The user can define how many assets to create as a new group, and how many to leave as part of the original group.

Quantity of duplicates is basically the value that should be assigned to ID 5 in the example. Really, though, I should have left this out of the question, because I know how to update this, and it's just extra information.

In the case of the example, the mouse exists for both workstation A B and C. But because all three are identical, they are grouped - so instead of creating A and B and C right away we just create A with quantity 3. We don't have to create extra copies of the mouse, because we know that there are three identical workstations. At time of the split, all of the identical subassets must be copied from A to create B. There are still three workstations and three mice, but now there are two groups - one group of two workstations and one group of one workstation.

Does that make sense?

Thanks!

Mary

Jay99

468 Posts

Posted - 2002-03-27 : 15:39:32

I am honestly trying to work on this but . .


Original 
ID Parent_Asset_ID AssetClass_ID Value Site_ID 
1  0              (Workstation)        99 
2  1              (Quantity)     2     99 
3  1              (Mouse)              99 
4  3              (Quantity)     1     99 

Desired Result: 
ID Parent_Asset_ID AssetClass_ID Value Site_ID 
1  0               (Workstation)       99 
2  1               (Quantity)    2     99 
3  1               (Mouse)             99 
4  3               (Quantity)    1     99 
5  0               (Workstation)       99 
6  5               (Quantity)    1     99 
7  5               (Mouse)             99 
8  7               (Quantity)    1     99

How do you go from a total of 2 workstations to a total of 3 workstations? How do you go from a total of 1 mouse to a total of 2 mouse(s)?
EDIT: WHY DOESN'T THIS LINE WRAP? I HATE THE WEB!! (joking)

How do you go from a total of 2 workstations to a total of 3 workstations?
How do you go from a total of 1 mouse to a total of 2 mouse(s)?

Look, inanycase, I think maybe the piece you are missing here is the required self joins . . .


select
    stuff
from
    asset
where
    id = @parm
union
select
    child.stuff
from
    asset parent
    inner join asset child
        on parent.id = child.parentid
where
    parent.id = @parm
union
select
    grandchild.stuff
from
    asset parent
    inner join asset child
        on parent.id = child.parentid
    inner join asset grandchild
        on child.id = grandchild.parentid
where
    parent.id = @parm
union
blahblahblah

You should be able to use that basic idea to do your duplication and your updates all together . . .

For the record, I am with RV on this...you probably need to re-thunk you model a bit.

Jay
<O>

Edited by - Jay99 on 03/27/2002 15:40:00

Edited by - Jay99 on 03/27/2002 15:40:50

Edited by - Jay99 on 03/27/2002 15:42:14

Phibian
Starting Member

32 Posts

Posted - 2002-03-28 : 18:09:55

While it didn't help that there was a typo in the table I provided with site ID where I listed workstation with quantity 2 instead of three, you are obviously still confused with my explanation of how the structure/real world mapping takes place. It's not that there is one mouse to start with, and then two to end up with - there's one mouse x number of workstations to start (should be 3), and then 1 mouse x 2 workstations + 1 mouse x 1 workstation.

Anyway - forget everything I said before. The number of mice etc doesn't really matter to my question - now that I've asked the question in several different ways, I've got a much more general question (probably what I should have asked in the first place, except I didn't know that yet <g>). Anyway, here goes:

Table:
ID | Parent ID
---------------
1 0
2 1
3 1
4 3
5 0
6 5

This translates to a hierarchy with at least three levels.
I want to duplicate ID 1 and all of its children, such that the new, duplicated hierarchy items have the right parent IDs.

I don't want to (and can't really) change the above structure - besides which, the current structure makes it really easy to make a list of the entire hierarchy (which we do all the time).
---------------------------------
The idea of a self join sounds VERY promising. But, my SQL skills are limited to pure SQL. If you could be a little more explicit (perhaps provide an example that would work for the sample data above?) it would really be helpful. I have tried it out, but am getting all kinds of very unhelpful errors (syntax error near '(') and syntax error near ',').

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources