I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this
Id-Hreid
Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get
Emp3-Emp2
Emp3-Emp1
Emp2-Emp1
I want an MDX calculated dimension member, (with no measure specified in expression, so that any related measure can be used in a query/browse), and that accomplishes either one of the following:
Ideal: To calculate Median values on every level of a user hierarchy
If "Ideal" is not possible, then "Acceptable" is: To calculates Median values on one or more levels OTHER than the highest level.
Hi,
I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..
Table Structure and Sample Data
Id ParentId Name Code DisplayOrder
1 null Group 1 G00001 1
2 null Group 2 G00002 2
3 1 Sub-Group 1 SG0001 1
4 2 Sub-Group 2 SG0002 1
5 3 Sub-Sub-Group 1 SSG001 1
6 null Group 3 G00003 3
7 3 Sub-Sub-Group 2 SSG002 2
Desired Result
Id ParentId Level Name ExtendedName DisplayOrder
1 null 1 Group 1 Group 1 1
3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1
5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1
7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2
4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1
2 null 1 Group 2 Group 2 2
6 null 1 Group 3 Group 3 3
How can I create a function that returns hierarchical data from a table with this structure:
- CategoryID
- CategoryName
- CategoryFather
I want to bring the result set like this...
CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2
How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.
Thanks.
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies View RelatedI am building three partitioned, clustered column store tables.I was researching whether it was faster to populate a staging table and swap it into the partitioned table or to directly insert into the partitioned table.The first partition for the three tables will have:
Table F: 50M rows, 6 columns wide, partitioned on a date column (1 date, 2 bigint keys, and two varchar columns)
Table D1: 50M rows, 150 columns wide, partitioned on a bigint
Table D2: 19M rows, 300 columns wide, partitioned on a bigint
If build the data that would go into partition 1 in a non partitioned column store, I get these table sizes:
Table F: 476 MB
Table D1: 6,800 MB
Table D2: 5,496 MB
If build the same data directly in the partitioned column store, my table sizes end up being:
Table F: 579 MB
Table D1: 6,800 MB
Table D2: 5,364 MB
That's a 20% difference on Table F, the narrow table.Looking at the row groups, I see 47 identical row groups in partition 1 and the unpartitioned table, but the average "size_in_bytes" is consistently 20% smaller in the unpartitioned table.
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View 3 Replies View RelatedWhen trying to link to an SQL table in Access 2003, the software appears to be malfunctioning.
The sequence of events is File - Get External Data - Link Tables - Files of Type: ODBC Databases().
The Problem: On two of my computers, the select data source window does not pop up, preventing me from linking to any ODBC data source.
Observations: This function has worked normally in the recent past and works on other computers running Access 2003. One difference between the computers working and non-working computers is Norton Antivirus 2006 (recent upgrade).
Has anyone experienced anything like this? What's going on?
In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.
I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.
I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?
Hi!
I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..
Date
Year
Quarter
Month
WeekDay
...
With some precalculated values for each date.
I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.
My first thought is to use some kind of Insert Cursor in a While loop...
Pseudo:
---------------------
declare cursor ex for 'Insert table ... '
while
begin
(produce data)
insert data
end
close cursor
---------------------
While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...
Have a nice day!
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
Dear all,
i need to design a database table which will store supplier's demand information. 1 supplier will probably have 10000 records and there are posibility that there are 10,000 suppliers. So, in total, the number of records will be 10000 * 10000 = XXXXA LOT XXXX which will be very large number of record to be inserted into a table. So, how can i design an table and structure to cater this scenario? Thanks.
Hope to hear from you..
I would like to know what is the preferred format for form submissions to a SQL table.
View 5 Replies View RelatedI have a C# app linked to a SQL db and I need to store it's version number in a table (could be something like 1.2.789) but I cannot find any datatype which allows me to do this.
I could create three fields in the table for each number but I don't want to.
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
View 2 Replies View RelatedSorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will
View 1 Replies View RelatedI am trying to create a sample table in the Azure SQL Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
[Code] ....
what's the correct syntax
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
How can I get the data from a table that stores hierarchical information keeping the right order and hierarchy levels?
I also need to specify a first level....let me explain.
Table: Sites
Id (PK)
Title
Url
ParentId
If I have something like this:
1 | Test | http://localhost | NULL
2 | Sub-Test | http://localhost/subtest | 1
3 | SecondItem | http://anotherServer | NULL
4 | ChildOfSub-Test | http://localhost/subtest/child | 2
And I specify that I want all child items below the Id 2, it must return:
Id | Title | Url | ParentId | Depth
2 | Sub-Test | http://localhost/subtest | 1 | 0
4 | ChildOfSub-Test | http://localhost/subtest/child | 2 | 1
How can I do this? I've saw some examples with CTEs, but they always return all of the items.
Hi, with the three tables below, I want to add relationships so that each Grandparent can spawn many parents and each Parent can spawn many children.
I keep getting an error when I try to connect them. Could I get some help creating that hierarchy please?
Thanks in advance!
Here are the tables:
CREATE TABLE [dbo].[GrandParent] (
[GPID] [int] IDENTITY (1, 1) NOT NULL ,
[GPName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent] (
[PID] [int] NOT NULL ,
[GPID] [int] NOT NULL ,
[PName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child] (
[CID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[ChName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--PhB
I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.
------XML-file---------------------------------------
<my:group1>
<my:group2>
<my:hustypTF>5</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>6</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>7</my:hustypTF>
</my:group2>
</my:group1>
-----------------------------------------------------
------T-SQL for fetching data from XML datatype------
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet
FROM MinaDekl
CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1)
-----------------
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
I've got a table that includes:CREATE TABLE [dbo].[Content] ( [Id] int IDENTITY(1, 1) NOT NULL, [ParentId] int NULL,
I'm wanting to make sure that a ParentId must be in the table as Id someplace else. When I try to do it by making it a foreign key get the error:
--------------- SQL ---------------
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
---------- ERROR MESSAGE ----------
There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors.
Any ideas?
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [Content].[dbo] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
[code].....
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),
[Code] .....
I have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.
CREATE TABLE [dbo].[t_prod_cat](
[cat_node_id] [bigint] IDENTITY(1,1) NOT NULL,
[advertiser_id] [bigint] NOT NULL,
[cat_hid] [hierarchyid] NULL,
[level] AS ([cat_hid].[GetLevel]()) PERSISTED,
CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED
(
[cat_node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.
Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"
CREATE TABLE [dbo].[t_prod_cat_work](
[temp_cat_node_id] [bigint] NOT NULL,
[temp_cat_hid] [hierarchyid] NOT NULL,
[advertiser_id] [bigint] NOT NULL
) ON [PRIMARY]
What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?
How to use hierarchy id data type in Bill of material database.
The msdn example [URL] ....
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
how to find the orphaned value from the below parent/child hierarchy Table.
create table dbo.Hier(parent varchar(100), child varchar(100))
insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5
View 3 Replies View RelatedHello,I am using SQL 2005 and Cognos' Data Manager. It is an ETL tool fordata warehousing.I have a problem with time it takes to load new changes, and I amseeking advice on a better way to manage the data.I have a table that tracks student attendance and it contains about 13million records. On a daily basis, there are 5,000 - 20,000 inserts and10,000 - 50,000 updates.The daily data comes for two different text files from my operationsystem; current and historical (CLSFIL and CLSHIS).The data is loaded into a staging area from the operational system,where data cleansing and other fields are added to the table.The final step is delivering the table to my target database, which isused for reporting.Heres the situation: I find it takes 45 minutes to do a relationalupdate, where only the records that changed in the last day will beloaded. However, if I choose the native API load instead of aRelational Load, it can load all 13M records in 7 minutes. The table isheavly indexedAt some point, the API load will take more time than the relationalload, (the changes and new records will remain a constant, but the filewill continue to grow).I'm seeking another solution is more efficient. I'm considering twotables for history and current and creating a view for reporting via aunion.This a good idea? How can I make the view effeicent to use the whereclause? Looking to bounce around ideas.Other Ideas?Thanks in AdvanceRob(I maintain the key relationships in the tool, not the tables. I knowI have lots to learn and improvments)CREATE TABLE "dbo"."F_BI_Class_Attendance_Detail"("CLASS_ATTENDANCE_ID" VARCHAR(50) NULL,"CLASSES_OFFERED_ID" VARCHAR(26) NULL,"CLASS_CAMPUS_ID" VARCHAR(10) NULL,"STUDENT_ID" CHAR(20) NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"SESSION_ID" CHAR(10) NULL,"SECTION_ID" VARCHAR(5) NULL,"MEET_DT" DATETIME NULL,"MEETING" SMALLINT NULL,"PRESENT" CHAR(2) NOT NULL,"SESSION_SKEY" BIGINT NULL,"STUDENT_SKEY" BIGINT NULL,"CLASS_CAMPUS_SKEY" BIGINT NULL,"CLASSES_OFFERED_SKEY" BIGINT NULL,"LOAD_DT" DATETIME NULL,"COMPUTED_DT" DATETIME NULL);
View 3 Replies View Relatedplease help me
i need for example
when my user clicke in the button a new table build in my sql database
please help me
I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.
My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.
select * from SourceTable -- produces 22461 records
--step 1: first, get row number of child records in each parent
SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;
[Code] ....