convert a table into tree

Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.

suppose table is like this. username parentid downline

B A left

C A right

D B left

E B right...

I have to also determine the level in the tree...please help...

CREATETABLE [dbo].[#tree2](

[username] [char](1),

[parentid] [char](1),

[downline] [nvarchar](50),

[id] [int]NOTNULL

)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('b','a','left',1)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('c','a','right',2)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('d','b','left',3)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('e','b','right',4)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('a',NULL,'right',5)

;WITH myCTE(levels, parentid, username, tree, downline)

AS

(SELECT 1, parentid, username,CAST(usernameasvarchar(50)),downline

FROM #tree2

WHERE parentidisNULL

UNIONALL

SELECT t1.levels+1,

t2.parentid,

t2.username,

CAST(tree+'/'+ t2.usernameASvarchar(50)),

t2.downline

FROM myCTE t1JOIN #tree2 t2ON t1.username=t2.parentid

WHERE t1.levels<10--you can change this up to the level you want

)

SELECT levels, parentid, username, downline, treeFROM myCTE

ORDERBY levels

DROPTABLE #tree2

Conversion of Access query using First() Aggregate

All,
I am trying to figure out how to convert this particular query, and am
stumped...
SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Category,
First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) AS
[FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
[Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
BalWhole
FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
tlListCategory.ListCatID
GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y
HAVING (((tCollatList.CollatID)=9));
SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit]'
Can someone please tell me what I can do, if anything to duplicate this
functionallity?
ThanksScottW wrote:
> All,
> I am trying to figure out how to convert this particular query, and am
> stumped...
> SELECT tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categor
y,
> First(tlListCategory.[CatWholeVal%]) AS [FirstOfCatWholeVal%],
> First(tlListCategory.Unit) AS FirstOfUnit, First(tlListCategory.[$Unit]) A
S
> [FirstOf$Unit], Sum([tCollatList]![ColListIncDec]) AS Balance,
> [Balance]*[FirstOf$Unit] AS BalValue, [BalValue]*[FirstOfCatWholeVal%] AS
> BalWhole
> FROM tCollatList LEFT JOIN tlListCategory ON tCollatList.ListCatID =
> tlListCategory.ListCatID
> GROUP BY tCollatList.CollatID, tCollatList.ListCatID, tlListCategory.Categ
ory
> HAVING (((tCollatList.CollatID)=9));
> SQL server doesn't support 'First(tlListCategory.[$Unit]) AS [FirstOf$Unit
]'
> Can someone please tell me what I can do, if anything to duplicate this
> functionallity?
> Thanks
The problem is that FIRST is not a real aggregate function, even in
Access. Access's FIRST and LAST functions just return some arbitrary
value from the set of rows in question. So you might as well use MIN or
MAX instead. If you want a different answer then you need a better
specification of what you mean by "first" and "last". Tables have no
inherent order and nor do query results unless you use ORDER BY.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hello David,
The code provided was specific, using SQL Server 2000, specifically it
says First is an unknown keyword.
As for there being arbitrary values, not in this case. Each value in the
joined table is identical, which is why we need only one record, so the
values from the other table can be summed. I guess that being said, usinfg
min or max should return the desired results. Thoughts?
Thanks
"David Portas" wrote:

> ScottW wrote:
> The problem is that FIRST is not a real aggregate function, even in
> Access. Access's FIRST and LAST functions just return some arbitrary
> value from the set of rows in question. So you might as well use MIN or
> MAX instead. If you want a different answer then you need a better
> specification of what you mean by "first" and "last". Tables have no
> inherent order and nor do query results unless you use ORDER BY.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||ScottW wrote:
> Hello David,
> The code provided was specific, using SQL Server 2000, specifically it
> says First is an unknown keyword.
That's right.

> As for there being arbitrary values, not in this case. Each value in the
> joined table is identical, which is why we need only one record, so the
> values from the other table can be summed.
In that case you can just add CatWholeVal%, Unit and $Unit to the GROUP
BY list. Then you don't need to use an aggregate function at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sqlsql

Context Change and Cursor

I have T-SQL code that is used to run utilities against a particular list of databases that are stored in a table. I have a cursor that gets the list of DBs from the sysdatabases table based on entries in a table in a specific database. The list of databases will vary from server to server, so I need to have it use variables rather than code the db names into the script.

declare @.DatabaseId char(8)

declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select <column name> from <db name..table> )

open DatabaseLoop
fetch next from DatabaseLoop into @.DatabaseId
while (@.@.fetch_status <> -1)
begin

...

So far, the context has not mattered, for example - backup @.DatabaseID works fine within the cursor since the context doesn't need to change. The problem is that I want to run particular scripts that require the context to be changed to the database. But, of course, USE @.DatabaseID does not work inside the cursor.

So, is there another way to be able to run my script against multiple variables other than using a cursor?

I am not necessarily looking for you to write my code, but if someone could point me in the right direction, I would appreciate it.

I would suggest that you use dynamic SQL for this and you can use a use in there.

declare @.query varchar(8000) -- I am assuming 2000 since you

--used sysdatabases

set @.query = 'use ' + @.database + ' select * from sysobjects'

exec (@.query)

I know this works in 2005, and am pretty sure it worked in 2000

Edit: Sorry, sent the message from my phone and it did a terrible job with the formatting

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL
|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>
|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enab
le
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data.
I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
>
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:
> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
> >
> > Isn't there a way, other then Enterprise Manager, to disable and / or enable
> > constraints, in particular primary and foreign keys? I am migrating data
> > daily from one system to SQL and to disable and enable manually is
> > inconvenient and combersome. I looked through BOL and cannot find a direct
> > answer on how to create a process to automatically disable and / or enable
> > constraints. Thanks.
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:
> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> > Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> > constraints, in particular primary and foreign keys?
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>