How to append records to tables in SQL query? [closed]
up vote
0
down vote
favorite
I have problem when use while in SQL query
This code is my table
SELECT TOP (1000) [Id]
,[ParentId]
,[RootId]
,[TreeStandardId]
,[ValueType]
,....
FROM [Trees]
DECLARE @maxDepth int
SELECT @maxDepth = MAX(depth) FROM @nodes
DECLARE @newTable table(id INT, parentId INT)
WHILE @maxDepth > 0
BEGIN
INSERT INTO @newTable
SELECT id, parentId
FROM Trees
WHERE Depth = @maxDepth
AND (ParentId IN (SELECT id FROM @nodes)
OR Id IN (SELECT id FROM @nodes)) union all select * from @newTable
SET @maxDepth = @maxDepth - 1;
END;
I want to append to logical table new found records but my code is just give one depth.this code get records by parent id or id and must be recursive to get all children
id parentId
29775 29774
29777 29775
29778 29775
29779 29777
29788 29777
i want get all records with this condition into table @newTable
for excample
depth 4 have 3 record
this 3 record have 2 records
@newTable = 3+2
Thanks for reading my problem
sql
sql-server asked Nov 10 at 18:05
Abcd Efgh
11
closed as unclear what you're asking by Sami, Madhur Bhaiya, Vega, eyllanesc, Makyen Nov 10 at 20:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
I have problem when use while in SQL query
This code is my table
SELECT TOP (1000) [Id]
,[ParentId]
,[RootId]
,[TreeStandardId]
,[ValueType]
,....
FROM [Trees]
DECLARE @maxDepth int
SELECT @maxDepth = MAX(depth) FROM @nodes
DECLARE @newTable table(id INT, parentId INT)
WHILE @maxDepth > 0
BEGIN
INSERT INTO @newTable
SELECT id, parentId
FROM Trees
WHERE Depth = @maxDepth
AND (ParentId IN (SELECT id FROM @nodes)
OR Id IN (SELECT id FROM @nodes)) union all select * from @newTable
SET @maxDepth = @maxDepth - 1;
END;
I want to append to logical table new found records but my code is just give one depth.this code get records by parent id or id and must be recursive to get all children
id parentId
29775 29774
29777 29775
29778 29775
29779 29777
29788 29777
i want get all records with this condition into table @newTable
for excample
depth 4 have 3 record
this 3 record have 2 records
@newTable = 3+2
Thanks for reading my problem
sql
sql-server asked Nov 10 at 18:05
Abcd Efgh
11
closed as unclear what you're asking by Sami, Madhur Bhaiya, Vega, eyllanesc, Makyen Nov 10 at 20:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
6
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have problem when use while in SQL query
This code is my table
SELECT TOP (1000) [Id]
,[ParentId]
,[RootId]
,[TreeStandardId]
,[ValueType]
,....
FROM [Trees]
DECLARE @maxDepth int
SELECT @maxDepth = MAX(depth) FROM @nodes
DECLARE @newTable table(id INT, parentId INT)
WHILE @maxDepth > 0
BEGIN
INSERT INTO @newTable
SELECT id, parentId
FROM Trees
WHERE Depth = @maxDepth
AND (ParentId IN (SELECT id FROM @nodes)
OR Id IN (SELECT id FROM @nodes)) union all select * from @newTable
SET @maxDepth = @maxDepth - 1;
END;
I want to append to logical table new found records but my code is just give one depth.this code get records by parent id or id and must be recursive to get all children
id parentId
29775 29774
29777 29775
29778 29775
29779 29777
29788 29777
i want get all records with this condition into table @newTable
for excample
depth 4 have 3 record
this 3 record have 2 records
@newTable = 3+2
Thanks for reading my problem
sql
sql-server asked Nov 10 at 18:05
Abcd Efgh
11
I have problem when use while in SQL query
This code is my table
SELECT TOP (1000) [Id]
,[ParentId]
,[RootId]
,[TreeStandardId]
,[ValueType]
,....
FROM [Trees]
DECLARE @maxDepth int
SELECT @maxDepth = MAX(depth) FROM @nodes
DECLARE @newTable table(id INT, parentId INT)
WHILE @maxDepth > 0
BEGIN
INSERT INTO @newTable
SELECT id, parentId
FROM Trees
WHERE Depth = @maxDepth
AND (ParentId IN (SELECT id FROM @nodes)
OR Id IN (SELECT id FROM @nodes)) union all select * from @newTable
SET @maxDepth = @maxDepth - 1;
END;
I want to append to logical table new found records but my code is just give one depth.this code get records by parent id or id and must be recursive to get all children
id parentId
29775 29774
29777 29775
29778 29775
29779 29777
29788 29777
i want get all records with this condition into table @newTable
for excample
depth 4 have 3 record
this 3 record have 2 records
@newTable = 3+2
Thanks for reading my problem
sql
sql-server sql
sql-server asked Nov 10 at 18:05
Abcd Efgh
11
asked Nov 10 at 18:05
Abcd Efgh
11
edited Nov 10 at 18:47
asked Nov 10 at 18:05
Abcd Efgh
11
asked Nov 10 at 18:05
Abcd Efgh
11
asked Nov 10 at 18:05
Abcd Efgh
11
11
closed as unclear what you're asking by Sami, Madhur Bhaiya, Vega, eyllanesc, Makyen Nov 10 at 20:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by Sami, Madhur Bhaiya, Vega, eyllanesc, Makyen Nov 10 at 20:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
6
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06
add a comment |
6
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06
6
6
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
6
Please provide sample data, desired results, and an explanation of the logic you want to implement.
– Gordon Linoff
Nov 10 at 18:06