Script to Generate current_Id based on Parent value

Question

Script to Generate current_Id based on Parent value

Naren-274001

Ten Centuries

Points: 1033
More actions
January 5, 2011 at 1:13 pm

#243222

Hii,
Please check the below, I pasted my requirement in the form of an image.
'ID_Current' is just an auto increment column but 'ID_parent' depends upon the parent node ID value.
CURRENT TABLE
IDID_ParentID_CurrentTitleID_Note
1 Apple1
2 Ball1.1
3 Berry1.1.1
4 Black1.1.1.1
5 Htc1.1.1.1.1
6 Evo1.1.1.1.1.1
7 air1.1.1.1.1.2
8 3G1.1.1.1.2
9 4G1.1.1.1.2.1
10 Android1.1.1.1.2.2
I need to write a script to fill ID_Parent and ID_Current as below
IDID_ParentID_CurrentTitleID_Note
10 1 Apple1
21 2 Ball1.1
32 3 Berry1.1.1
43 4 Black1.1.1.1
54 5 Htc1.1.1.1.1
65 6 Evo1.1.1.1.1.1
75 7 air1.1.1.1.1.2
84 8 3G1.1.1.1.2
98 9 4G1.1.1.1.2.1
108 10 Android1.1.1.1.2.2
In this way, I need to fill 5000 rows. So I need to implement the script to do this task
Can anyone help with code direction for implement the script to generate 'ID_parent' based on the 'ID_Node' and 'ID_Current'
I have to generate parent_ID based on the the value we have in ID_Node (something like Tree)
For example :
The 1st value for ID_Node is 1 and Value is 'Apple'. So I will assume ID_Parent as 0 and ID_Current as 1
now 2nd value ID_Node is 1.1 and Value is 'Ball'. So I will set ID_Parent as 1 and ID_Current as 2( 'coz 1.1 already has the parent value 1 )
now 5th value ID_Node is 1.1.1.1 and value is Htc'.So I will set ID_Parent as 4 and ID_Current as 5
When you look at 1.1.1.1.1.1 & 1.1.1.1.1.2 i.e. ID_Node 6th and 7th
I am assuming the portion before .1 and .2 as parent, which is 5 so the ID_Parent for 6th and 7th will be 5 and ID_current will just increament by 1
so now while coding If I want to get the full tree for 'Air', whose ID_parent = 5 and ID_current = 7
I will know the tree is
Apple -> Ball-> Berry Black -> HTC-> AIR
and for 'Evo' it will be
Apple -> Ball-> Berry Black -> HTC-> Evo
So I have to write a script for filling ID_parent field, for ID_Current it is just the next value….
For ID_Parent,
I need to take id_note value trim last two characters and when I get the value search for it in id_note filed and find its corresponding id and insert that vaule in id_parent.
This is what I want to do
Thankyou
Naru

Viewing 5 posts - 1 through 4 (of 4 total)

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

Here's a script for providing data in an easily consumable format. I've made some assumptions about your table structure.

CREATE TABLE #MyTable (

ID INT NOT NULL PRIMARY KEY,

ID_Parent INT NULL,

ID_Current INT NULL,

Title varchar(40) NOT NULL,

ID_Note varchar(1000) NOT NULL

)

INSERT INTO #MyTable (ID, Title, ID_Note)

SELECT 1, 'Apple', '1' UNION ALL

SELECT 2, 'Ball', '1.1' UNION ALL

SELECT 3, 'Berry', '1.1.1' UNION ALL

SELECT 4, 'Black', '1.1.1.1' UNION ALL

SELECT 5, 'Htc', '1.1.1.1.1' UNION ALL

SELECT 6, 'Evo', '1.1.1.1.1.1' UNION ALL

SELECT 7, 'air', '1.1.1.1.1.2' UNION ALL

SELECT 8, '3G', '1.1.1.1.2' UNION ALL

SELECT 9, '4G', '1.1.1.1.2.1' UNION ALL

SELECT 10, 'Android', '1.1.1.1.2.2'

Here's a script to populate the ID_Current and ID_Parent columns. The UPDATE query is quite simple once you have worked out how to derive the ID_Note column value of the parent row from the ID_Note column value of the current row.

UPDATE T SET

ID_Current = T.ID,

ID_Parent = CASE WHEN (P.ID <> T.ID) THEN P.ID ELSE 0 END

FROM #MyTable T

LEFT OUTER JOIN #MyTable P

ON (P.ID_Note = SUBSTRING(T.ID_Note, 1, LEN(T.ID_Note) - CHARINDEX('.', REVERSE(T.ID_Note))))

EDIT: I've simply copied the ID column value to the ID_Current column. This works for your test data, but maybe your requirements are not quite this straightforward(?)

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

Narendra-274001

I just noticed the other thread in the T-SQL 2008 forum.

PLEASE don't cross post - you just waste people's time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No more replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1043259-392-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Naren-274001 Ten Centuries Points: 1033 More actions · Answer 4

Hi,

Sorry about that.

I will not repeat it again.

I did not realize I posted on Sql Server 2008. Hence posted in Sql Server 2005 again.

As I was getting response on Sql Server 2008, I posted but did not touch this one.

Thankyou

Narendra