Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Creating tables in SQL Server via query analyzer with relationships

I've been searching around for some info on how to set this up, but with no luck.
I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.
I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.
For instance, I can create the tables just fine....
CREATE TABLE students ( sId int NOT NULL PRIMARY KEY,
studentId varchar(50) NOT NULL,
course varchar(50)
)

CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY,
course varchar(50) NOT NULL,
sco varchar(50)
)
But, I need to set up relationships in there somehow.
Once student may have many courses (one to many) and one course may have many sco's (one to many)
SCO would be another table.
Can someone point me to a good link that would show how to complete these procedures?
Thanks all,
Zath

"sId"? "StudentId"?? "cId"?? Great start for a disaster of a system right there. If that's just a taste of your schema, you need to scrap it and start over. If you don't understand what's so wrong with it, spend a few hours reading about the relational model.
Anyway, you need to look in the BOL on DDL statements. Foreign keys are implemented via constraints:
CREATE TABLE Order_Details
(
Order_Num CHAR(12) NOT NULL
CONSTRAINT FK_Order_Detail_Orders
FOREIGN KEY (Order_Num)
REFERENCES Orders (Order_Num),
Prod_Num CHAR(8) NOT NULL
CONSTRAINT FK_Order_Detail_Products
FOREIGN KEY (Prod_Num)
REFERENCES Products (Prod_Num),
CONSTRAINT FK_Order_Details
PRIMARY KEY CLUSTERED (Order_Num, Prod_Num)
)

|||An easy way to do this, is set up your tables via enterprise manager, and then use All Tasks > Generate SQL Script. Inside the script generator, you can click on the Options tab, and click script primary keys, foreign keys, defaults and check contraints.
Nick|||Check out this T-SQL reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp

There are several ways to do it, but one of these should work

B. Use FOREIGN KEY constraints

A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This example shows a single-column FOREIGN KEY constraint on the employee table that references the jobs table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)

You can also explicitly use the FOREIGN KEY clause and restate the column attribute. Note that the column name does not have to be the same in both tables.

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

Multicolumn key constraints are created as table constraints. In the pubs database, the sales table includes a multicolumn PRIMARY KEY. This example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)|||Thanks everyone for their input. I have already gotten rid of the sID and so forth or rather didn't make them primary keys, just an autonumber.
It's been a while since I developed a database, I like to stick to code and need a database refresher it seems.
Once I redo this, if I have any other problems, I repost.
Thanks,
Zath

Creating relationships with MSDE

Is it possible to create relationships between tables using foregin keys with MSDE. I have looked for any information and can't seem to find anything.With MSDE you can use Visual Studio and MS Access. With Access you have to create new Data Project. Also you can use SQL Server Enterprise Manager to create relationships. But, you can be always more elite and use oSQL and create relations using SQL.

If you prefer writing SQL for relations then you should read T-SQL documentation. Check out chapters for CREATE TABLE and ALTER TABLE statements.|||Yes. You can create realationships as mentioned. Is there specific information you would like to know.|||Thanks Guys.

Helps to know that I wasn't going to crazy trying to figure it out.

Creating relationships in asp.net enterprise manager

Hello

I am using a web hosting company for my web site, unfortunately after developing the site with server express the company told me they do not support this. As the database only holds three tables and only contained test data, this was not too much of a problem and I thought I would rebuild the databse on the development tool (ASP.net enterprise manager). I have managed to create the tables and populate them with the required data. My only problem is the user interface of the enterprise manager is not the friendliest and I am not too sure how to create the relationships between the tables. The interface only seems to have facilities for creating tables, views, stored procedures, users and roles.

Any tips on how I can create relationships between the tables.

Graeme

In SQL Enterprise Manager right-click on a table, and select Design. A design window will open from the table. What you want is on the SQL Enterprise main window (parent window) - the 3 icons on the right: Manage Constraints, Manage Indexes, and Manage Relationships. clicking on anyone of them opens the same dialog, which lets you manage any of the three.

Good luck.

|||

Hi Alex

I think the interface the webhosting company allows me to use is not the actual program as it does not allow right clicking; all the images and tables etc seem to be hyperlinks. I am trying to get around this by hardcoding the tables in SQL and then running the queries.

|||

Hi,

From your description, it seems that you can't use some functions of enterprise manager in the website based enterprise manager they provided, right?

Right, actually, the website based enterprise manager is a web application which runs the underlying SQL command for your specific operations. (such as create,drop tables, insert records and etc..) And the function you can use is totally based on your hosters, since it's not a real Enterprise Manager of SQLServer. So as you mentioned, if the interface only seems to have facilities for creating tables, views, stored procedures, users and roles, but not for creating relations, you have to handle it in another way.

Based on my understanding, lot's of hosters are support remote connections. So you may connect to the database instance on your hoster's server by IP address and userid, passwords. And then export the data on your local instance to the remote instance, or you can just create your tables on remote instance directly and handle the relation by the real Enterprise Manger tool.

Thanks.

Creating relationships between tables in different databases

Hello guys,
Does anybody know how can i create a relationship between tables in differen
t databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different d
atabases and i'd like to have some kind of referencial integrity in the data
that is stored on the separated databases by using the same shared tables.
I could use triggers for do
ing that but this will decrease the performance of my databases dramatically
.
Thanks for the help,
Paulo R.Cross database constraints are not supported. You would need
to go the route along the lines of triggers to achieve
something like this.
-Sue
On Mon, 29 Mar 2004 13:41:08 -0800, "paulo"
<anonymous@.discussions.microsoft.com> wrote:

>Hello guys,
>Does anybody know how can i create a relationship between tables in differe
nt databases? Does SQL Server supports this kind of operation?
>The main problem is that i have some data that is shared between different database
s and i'd like to have some kind of referencial integrity in the data that is stored
on the separated databases by using the same shared tables. I could use triggers fo
r d
oing that but this will decrease the performance of my databases dramatically.d">
>Thanks for the help,
>Paulo R.

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Even if SQL Server did support it (which I don't believe it does) it would
be a very poor design. What if the databases were installed on separate
servers? That would be a real mess. If the tables are that important for a
database then some sort of replication from one database to the other would
be a better solution.
Jim
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:4B7733D7-AFCE-4992-9734-59C7FA38194A@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||>>Does SQL Server supports this kind of operation?<<
Cross database ref. integrity is not supported in SQL Server.
BOL: "FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For more
information, see CREATE TRIGGER. "
So this can be accomplished through triggers.
See following topic in bol.
"FOREIGN KEY Constraints"
Vishal Parkar
vgparkar@.yahoo.co.in

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider

Creating relationships between 2 databases

Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?
This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully.
I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?
So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL.

Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.

? No, it is not possible. If your only goal is to get around the 4gb limit, then my suggestion is that you upgrade to Workgroup Edition, which has no such limitation. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:d2141cdb-833b-49ba-8bc4-98feb1cd8a83@.discussions.microsoft.com... Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully. I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL. Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.|||

No NNTP User,

my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that.

Can you think of any workaround ?

|||? You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:7915ff66-3526-4b90-aea2-8762ed87d8dc@.discussions.microsoft.com... No NNTP User, my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that. Can you think of any workaround ?|||To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||

You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution.

I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?

|||? Actually, you only need distributed transactions if you're going across servers or instances. Cross-database work in the same instance will not need to make use of a distributed transaction. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <bass_player@.discussions.microsoft.com> wrote in message news:793732c8-c600-43b9-8e1a-294100137ac6@.discussions.microsoft.com...To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||? Unfortunately, you can't do most of this stuff via the UI. DML is Data Manipulation Language; DML triggers fire on DML events such as inserting, updating, or deleting data. I recommend that you pick up a good basic SQL book to start with. I'm not sure if there are any out for SQL Server 2005 yet, but you might just start with a SQL Server 2000 one. I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:b6185a4a-9770-456b-86e3-feacd72df877@.discussions.microsoft.com... you can also wrap them in distributed transactions. You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?|||

Rishi, it is not possible to enforce foreign key relationship across database. NNTP is right, you can use triggers. Triggers are programs which runs when there is a new rows created in a table. Or when there is a row deleted from a table. Or when a row is updated. That's DML trigger, there is something else called DDL trigger, which is a new feature in 2005, but let's not talk about this right now.
Compared to foreign key (FK), this DML trigger has a performance issue, it will make the insert (or delete or update) slower than usual. But it has advantages: it is more flexible than FK. A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
Vincent

|||

A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.

But for that, I would have to write code, wouldn't I ? I mean, I cannot do all this stuff using the GUI ?

Anyway, I wouldn't want to implement a design which slows down the performance of the database much. Thanks for all the help guys.

Rishi.

|||

Say, is there anyway I can create the relationship in the front end application using VB Express ?

Because I really want to put all the info into one application. Its like having everything under one roof. A complete ERP solution ( for small proprietory concerns with no employees, not counting peons ). I have seen many organisations use different software for different purposes and I did not like the idea. For instance, they use different software for keeping track of their inventory, employee details (which covers employee attendance, performance, incentives etc.), financial records etc. Now for example, I have the inventory software, which has a separate table which stores employee names, and this is used so that the sales reports show the employee wise sales, but all the main employee information is stored in the database of another application altogether. Now, suppose I am the manager, and I am going through the sales by emp report, and I suddenly feel I want to know more details about the emp, so in this scenario, I would have to quit the inventory program, fire up the employee program, and then look up the employee's name, and then pull up the emp's info. Now wouldn't it be nice if I had both the databases linked in the front end application, so I could directly pull up the employee's info ? Why I don't want to put both these separate pieces of info in the same database is again performance, and I need to clarify this point - If I have both the inventory and the employee databases from the above example consolidated into a single database, would it slow down the performance of the database as it grows larger in size ? Or would it offer performance benefits if they are kept in separate dbs and are linked through the front end.

I know the idea given to me above about using DML triggers would considerably slow down the database, as the program would have to scan every record every time a record is created or updated or deleted. So I have scrapped the plan of using DML triggers ( after learning DML of course )

Any ideas about using the VB Express created front end application though ?

|||

>>I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson.

Anyone know if there's going to be a SQL Server 2005 version of this book? I just "discovered" it last night at B&N; it looks like a great book. However, there's nothing on Amazon.com nor the publisher's site about an upcoming revised edition.

creating new sql database

Hi,
I am creating a new database with medium complexity, will have around 15
tables with most of them related by key relationships. I am planing on using
mssql2000, enterprise manager but I also have vs2005 installed. Just
wondering if there is any advantage in creating a database project in vs2005
and is that what it is for(to create new databases?) We do not have sql 2005
yet other than the developers version that comes with vs2005 (does not allow
networking).
Thanks,
Paul G
Software engineer.
Once you've collected the business requirements and designed the database ,
you can use Diagrams.

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:06DDA0DB-B543-4C13-9A5C-ECBCBF7CA81B@.microsoft.com...
> Hi,
> I am creating a new database with medium complexity, will have around 15
> tables with most of them related by key relationships. I am planing on
> using
> mssql2000, enterprise manager but I also have vs2005 installed. Just
> wondering if there is any advantage in creating a database project in
> vs2005
> and is that what it is for(to create new databases?) We do not have sql
> 2005
> yet other than the developers version that comes with vs2005 (does not
> allow
> networking).
> Thanks,
> --
> Paul G
> Software engineer.

creating new sql database

Hi,
I am creating a new database with medium complexity, will have around 15
tables with most of them related by key relationships. I am planing on using
mssql2000, enterprise manager but I also have vs2005 installed. Just
wondering if there is any advantage in creating a database project in vs2005
and is that what it is for(to create new databases?) We do not have sql 2005
yet other than the developers version that comes with vs2005 (does not allow
networking).
Thanks,
--
Paul G
Software engineer.Once you've collected the business requirements and designed the database ,
you can use Diagrams.
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:06DDA0DB-B543-4C13-9A5C-ECBCBF7CA81B@.microsoft.com...
> Hi,
> I am creating a new database with medium complexity, will have around 15
> tables with most of them related by key relationships. I am planing on
> using
> mssql2000, enterprise manager but I also have vs2005 installed. Just
> wondering if there is any advantage in creating a database project in
> vs2005
> and is that what it is for(to create new databases?) We do not have sql
> 2005
> yet other than the developers version that comes with vs2005 (does not
> allow
> networking).
> Thanks,
> --
> Paul G
> Software engineer.