Tree view concept - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Tree view concept

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-29 : 04:42:21

Hi all,

this is my table

CREATE TABLE [dbo].[progressnotes](
[NotesId] [int] IDENTITY(1,1) NOT NULL,
[NotesName] [varchar](200) NOT NULL,
[ControlType] [varchar](100) NULL,
[ParentNotesId] [int] NULL,
[Depth] [tinyint] NULL,
[Order] [tinyint] NULL,
[Others] [varchar](8000) NULL,
[ISCOMMON] [bit] NOT NULL
) ON [PRIMARY]

So its like hierarchial concept. each noteid has its parent noteid.
table is ok now. but i need to have a duplicate of Notes now.

Say Notes A has its subset
NotesA1
NotesA2
NotesA3
NotesA31
NotesA4

Say i have another NotesB. This NotesB should be replicate of NotesA
and it will have only NotesA1,Notes A2 has its subset
how to do. Pls

Iam a slow walker but i never walk back

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 05:00:45

what determines what all value you want to replicate for new id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 07:25:17

Are NotesA, NotesA1, NotesA2, etc stored in the NoteName field? How many hierarchical levels are there? eg, do NoteA1 and NoteA2 have child notes that also need to be copied?

There are 10 types of people in the world, those that understand binary, and those that don't.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-29 : 07:38:53

quote:
Originally posted by visakh16

what determines what all value you want to replicate for new id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi.

Scenario is like this.

NotesA
NotesA1
NotesA2
NotesA21
NotesA22
NotesA3
NotesA4

so when i want to get subnotes under NotesA
everything will get displayed

I have add another Notes say "NotesCopyA" which is a copy of NotesA
but doesnt include all the SubNotes.
NotesCopy
NotesA1
NotesA2
NotesA21
----NotesA22
----NotesA3
NotesA4

see i have commented the sub notes with ---- So NotesCopyA is replicate of NotesA
but avoids some subnotes

Help Please

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-29 : 07:54:10

quote:
Originally posted by DBA in the making

Are NotesA, NotesA1, NotesA2, etc stored in the NoteName field? How many hierarchical levels are there? eg, do NoteA1 and NoteA2 have child notes that also need to be copied?

There are 10 types of people in the world, those that understand binary, and those that don't.

yes NotesA, NotesA1, NotesA2, etc stored in the NoteName.
Hierarchial Levels can be upto 3 levels.
Yes NotesA1 and NotesA2 have same child notes. but varies on Copy Notes.

Iam a slow walker but i never walk back

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 11:59:39

quote:
Originally posted by dineshrajan_it

quote:
Originally posted by visakh16

what determines what all value you want to replicate for new id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi.

Scenario is like this.

NotesA
NotesA1
NotesA2
NotesA21
NotesA22
NotesA3
NotesA4

so when i want to get subnotes under NotesA
everything will get displayed

I have add another Notes say "NotesCopyA" which is a copy of NotesA
but doesnt include all the SubNotes.
NotesCopy
NotesA1
NotesA2
NotesA21
----NotesA22
----NotesA3
NotesA4

see i have commented the sub notes with ---- So NotesCopyA is replicate of NotesA
but avoids some subnotes

Help Please

Iam a slow walker but i never walk back

thats ok. but where will you hold information which suggests NotesA1,NotesA2 etc are required for new copy while NotesA22 etc are not required?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-30 : 02:56:25

quote:
Originally posted by visakh16

quote:
Originally posted by dineshrajan_it

quote:
Originally posted by visakh16

what determines what all value you want to replicate for new id?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi.

Scenario is like this.

NotesA
NotesA1
NotesA2
NotesA21
NotesA22
NotesA3
NotesA4

so when i want to get subnotes under NotesA
everything will get displayed

I have add another Notes say "NotesCopyA" which is a copy of NotesA
but doesnt include all the SubNotes.
NotesCopy
NotesA1
NotesA2
NotesA21
----NotesA22
----NotesA3
NotesA4

see i have commented the sub notes with ---- So NotesCopyA is replicate of NotesA
but avoids some subnotes

Help Please

Iam a slow walker but i never walk back

thats ok. but where will you hold information which suggests NotesA1,NotesA2 etc are required for new copy while NotesA22 etc are not required?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi. Thats what i need to put. I dont know how to map that.

Iam a slow walker but i never walk back

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 09:08:10

Here's one way to do it, using a temporary table, and two stored procs that relies on the temp table being created and populated before being call. One will copy the note based on the NotesName of the source note, and the other based on the NotesID of the source note.

Be warned though, this procedure relies heavily on matching based on the NotesName column. It does not rename any child notes when copying them over to the new note. It will copy the original root note record into a new record.

It also makes the assumption that you have a structure of:
Parent
---Child Level 1
------Child Level 2

Any children deeper than that will not be copied.

Also, this the CopyNoteByName will fail if there are 2 note records with the same source name. eg, you pass it 'NoteA', 'NoteB', it will fail if there are already 2 'NoteA' records

Firstly, here that routine I used to create the sample data:


CREATE TABLE [dbo].[progressnotes](
[NotesId] [int] IDENTITY(1,1) NOT NULL,
[NotesName] [varchar](200) NOT NULL,
[ControlType] [varchar](100) NULL,
[ParentNotesId] [int] NULL,
[Depth] [tinyint] NULL,
[Order] [tinyint] NULL,
[Others] [varchar](8000) NULL,
[ISCOMMON] [bit] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[progressnotes] 
	([NotesName], [ControlType], [ParentNotesId], [Depth], [Order], [Others], [ISCOMMON])
SELECT 'NoteA', 'None', NULL, 1, 1, NULL, 0
UNION ALL SELECT 'NoteA1', 'Type 1', 1, 2, 1, NULL, 0
UNION ALL SELECT 'NoteA2', 'Type 2', 1, 2, 2, NULL, 0
UNION ALL SELECT 'NoteA3', 'Type 3', 1, 2, 3, NULL, 0
UNION ALL SELECT 'NoteA31', 'Type 3a', 4, 3, 1, NULL, 1
UNION ALL SELECT 'NoteA32', 'Type 3b', 4, 3, 2, NULL, 1
UNION ALL SELECT 'NoteA4', 'Type 4', 1, 2, 4, NULL, 0
UNION ALL SELECT 'NoteA41', 'Type 4a', 7, 3, 1, NULL, 0
UNION ALL SELECT 'NoteA42', 'Type 4b', 7, 3, 2, NULL, 0
GO

Then there's the 2 stored procs. These procedures both return the ID of the created root note.


CREATE PROCEDURE [dbo].[CopyNoteByID]
@SourceNoteNameID INT,
@DestNoteName VARCHAR(200)
AS 
DECLARE @NewRootNoteID INT

	-- Copy the parent Note
	INSERT INTO [dbo].[progressnotes] 
		([NotesName], [ControlType], [ParentNotesId], [Depth], [Order], [Others], [ISCOMMON])
	SELECT @DestNoteName, [ControlType], [ParentNotesId], [Depth], [Order], [Others], [ISCOMMON]
	FROM [dbo].[progressnotes] 
	WHERE [NotesID] = @SourceNoteNameID

	SELECT @NewRootNoteID = MAX([NotesID])
	FROM [dbo].[progressnotes] 
	WHERE [NotesName] = @DestNoteName 

	-- Copy the children (level 1)
	INSERT INTO [dbo].[progressnotes] 
		([NotesName], [ControlType], [ParentNotesId], [Depth], [Order], [Others], [ISCOMMON])
	SELECT n.[NotesName], [ControlType], 
		@NewRootNoteID, 
		[Depth], [Order], [Others], [ISCOMMON]
	FROM [dbo].[progressnotes] n
	INNER JOIN #NotesToCopy c
		ON n.[NotesName] = c.[NotesName]
	WHERE n.[ParentNotesId] = (SELECT [NotesId] FROM [dbo].[progressnotes] WHERE [NotesID] = @SourceNoteNameID)

	
	-- Copy the children (level 2)
	INSERT INTO [dbo].[progressnotes] 
		([NotesName], [ControlType], [ParentNotesId], [Depth], [Order], [Others], [ISCOMMON])
	SELECT n.[NotesName], n.[ControlType], 
		--(SELECT [NotesId] FROM [dbo].[progressnotes] WHERE [NotesName] = @DestNoteName), 
		s.NotesID,
		n.[Depth], n.[Order], n.[Others], n.[ISCOMMON]
	FROM [dbo].[progressnotes] n
	INNER JOIN [dbo].[progressnotes] p
		ON n.ParentNotesID = p.NotesID
	INNER JOIN [dbo].[progressnotes] s
		ON p.NotesName = s.NotesName
		AND s.ParentNotesID = @NewRootNoteID
	INNER JOIN #NotesToCopy c
		ON n.[NotesName] = c.[NotesName]
	WHERE n.[ParentNotesId] IN (
			SELECT [NotesId] 
			FROM [dbo].[progressnotes] 
			WHERE [ParentNotesId] = (SELECT [NotesId] FROM [dbo].[progressnotes] WHERE [NotesID] = @SourceNoteNameID))

	RETURN @NewRootNoteID
GO 

CREATE PROCEDURE [dbo].[CopyNoteByName]
@SourceNoteName VARCHAR(200),
@DestNoteName VARCHAR(200)
AS 
DECLARE @RC INT
DECLARE @SourceNoteNameID INT
	
	SELECT @SourceNoteNameID = [NotesID]
	FROM [dbo].[progressnotes]
	WHERE [NotesName] = @SourceNoteName
		AND [ParentNotesID] IS NULL

	EXECUTE @RC = [AdventureWorks].[dbo].[CopyNoteByID] 
		@SourceNoteNameID
		,@DestNoteName
	
	RETURN @RC
GO

This is how you call them. First, you create a temp table and populate it. The table name must be as specified, as the sp will attempt to join onto this table. This also means the create table routine and the sp call must share the same connection.

Once the table is populated, you can call the proc, passing it the Source NoteName or NoteID (depending on which sp you call), and the NoteName of the copy to be created. It won't modify the names of any child notes, so you'll need to handle if required. Let me know if you have any problems.

The temp table has a clustered index on it. This was required to make the insert happen in the correct order. Without it, the new child notes were not inserted in the same order as the original child notes.

This however does cause a slight issue. If 2 of these temp tables are created in separate connections, then the second will fail, because the index name is already being used. If you need to allow for this, then you'll need to dynamically create a unique index name. It doesn't really matter what it is, as long as it's not already being used. Or, if the insert order is not an issue, just remove the index altogether.


-- Create a temp table to hold child NoteNames to be copied
CREATE TABLE #NotesToCopy ([NotesName] VARCHAR(200)
 CONSTRAINT [PK_temp_NotesToCopy_NotesName] PRIMARY KEY CLUSTERED 
(
	[NotesName] ASC
))
GO 

-- Populate the temp table with child NoteNames to copy
INSERT INTO #NotesToCopy
SELECT 'NoteA2'
UNION ALL SELECT 'NoteA3'
UNION ALL SELECT 'NoteA31'
UNION ALL SELECT 'NoteA4'
UNION ALL SELECT 'NoteA41'
UNION ALL SELECT 'NoteA42'
GO 

EXEC [dbo].[CopyNoteByID]  1, 'NoteB'
EXEC [dbo].[CopyNoteByName]  'NoteB', 'NoteC'

SELECT * FROM [dbo].[progressnotes]

-- Don't forget to drop the temp table when done.
DROP TABLE #NotesToCopy

There are 10 types of people in the world, those that understand binary, and those that don't.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-31 : 02:29:27

Hi,

Thanks for taking ur time for this issue. i will work it out and let you know.

Thanks again.

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources