alternatives to adjacency model? (hierarchical data)

Hello Gurus,
I am looking for a hierarchical modeling technique other than
adjacency.
Simplicity is the goal. Updates and inserts must be reasonably easy.
For example, I'd like to see some modeling options other other this:
CREATE TABLE [Hier] (
[ID] [int] NOT NULL ,
[PARENT_ID] [int] NULL ,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Parent] FOREIGN KEY
(
[PARENT_ID]
) REFERENCES [Hier] (
[ID]
)
)
Anyone know of any (links are often helpful)?
Thank you,
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1123788975.065397.6950@.g47g2000cwa.googlegroups.com...
> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
>
Well, there's the nested set model. But it simplifies nested searching and
complicates inserts and updates.
For instance in this example
http://groups-beta.google.com/group...b60b7151?hl=en&
This is required to insert a node
BEGIN
DECLARE right_most_sibling INTEGER;
SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = :your_boss);
UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;
INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling, (right_most_sibling + 1))
END;
And you probably should introduce a transaction and take a TABLOCKX on
Personnel to control concurrency.
David|||Your model lacks circular reference prevention. Consider something like the
solution suggested in this thread:
http://msdn.microsoft.com/newsgroup...891d&sloc=en-us
ML|||Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4
SQL Lessons
http://www.dbmsmag.com/9604d06.html
Maintaining Hierarchies
http://www.windowsitpro.com/Article...=glance&s=books
AMB
"n_o_s_p_a__m@.mail.com" wrote:

> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
> For example, I'd like to see some modeling options other other this:
> CREATE TABLE [Hier] (
> [ID] [int] NOT NULL ,
> [PARENT_ID] [int] NULL ,
> CONSTRAINT [PK] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Parent] FOREIGN KEY
> (
> [PARENT_ID]
> ) REFERENCES [Hier] (
> [ID]
> )
> )
> Anyone know of any (links are often helpful)?
> Thank you,
> -KJ
>|||Go out and buy a copy of TREES & HIERARCHIES IN SQL. It will save you
a lot of trouble and pay my mortgage.

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass withou
t
having to insert/update each row individually. You could use DTS, BULK INSER
T
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:

> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are the
re
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.

> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.

> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT[vbcol=seagreen]
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
>
Stored[vbcol=seagreen]
there[vbcol=seagreen]|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:

> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
> staging
> without
> INSERT
> Stored
> there
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
>
from[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
Inserts/[vbcol=seagreen]
Are[vbcol=seagreen]|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:

> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> from
> to
> the
> Inserts/
> Are
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
>
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.

>
> without
> INSERT
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> command,
the[vbcol=seagreen]
> staging
> without
> INSERT
Inserts/[vbcol=seagreen]
> Stored
> there
>

Alternative to OSQL?

SQL2K SP4
Howdy all. I have a daily process that gets a .txt file full of Inserts/
Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
any alternatives to this from within SQL Server?
TIA, ChrisRHi Chris
You could write your own application to parse the file and run each command,
a different alternative would be to load the data into a table and use the
EXEC command to run them. As your statements are in a file, there is a
security risk. If you just provided a data file and loaded it into a staging
table you could then insert/update existing data quite easily on-mass without
having to insert/update each row individually. You could use DTS, BULK INSERT
or BCP to quickly load the data into a staging table.
John
"ChrisR" wrote:
> SQL2K SP4
> Howdy all. I have a daily process that gets a .txt file full of Inserts/
> Updates/ Deletes and imports them into a SQL DB every day. I have a Stored
> Proc that calls a .bat which contains OSQL and runs the .txt file. Are there
> any alternatives to this from within SQL Server?
> TIA, ChrisR
>
>|||I appreciare your ideas, but Im confused.
> You could write your own application to parse the file and run each
command
I really need to do this from within SQL Server.
> If you just provided a data file and loaded it into a staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
How would this be any better than what I have? I still need to get it from
the file into a table. The way Im reading this, I need to go from file to
table, then from table to table?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> Hi Chris
> You could write your own application to parse the file and run each
command,
> a different alternative would be to load the data into a table and use the
> EXEC command to run them. As your statements are in a file, there is a
> security risk. If you just provided a data file and loaded it into a
staging
> table you could then insert/update existing data quite easily on-mass
without
> having to insert/update each row individually. You could use DTS, BULK
INSERT
> or BCP to quickly load the data into a staging table.
> John
> "ChrisR" wrote:
> > SQL2K SP4
> >
> > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > Updates/ Deletes and imports them into a SQL DB every day. I have a
Stored
> > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
there
> > any alternatives to this from within SQL Server?
> >
> > TIA, ChrisR
> >
> >
> >|||Hi Chris
How do you create the file that makes up these SQL Statements?
John
"ChrisR" wrote:
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>
>|||It is made up for me. A mainframe does some stuff, an ETL tool called Tree
House does some stuff, etc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> Hi Chris
> How do you create the file that makes up these SQL Statements?
> John
> "ChrisR" wrote:
> > I appreciare your ideas, but Im confused.
> >
> > > You could write your own application to parse the file and run each
> > command
> >
> > I really need to do this from within SQL Server.
> >
> > > If you just provided a data file and loaded it into a staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> >
> > How would this be any better than what I have? I still need to get it
from
> > the file into a table. The way Im reading this, I need to go from file
to
> > table, then from table to table?
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > Hi Chris
> > >
> > > You could write your own application to parse the file and run each
> > command,
> > > a different alternative would be to load the data into a table and use
the
> > > EXEC command to run them. As your statements are in a file, there is a
> > > security risk. If you just provided a data file and loaded it into a
> > staging
> > > table you could then insert/update existing data quite easily on-mass
> > without
> > > having to insert/update each row individually. You could use DTS, BULK
> > INSERT
> > > or BCP to quickly load the data into a staging table.
> > >
> > > John
> > >
> > > "ChrisR" wrote:
> > >
> > > > SQL2K SP4
> > > >
> > > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > Stored
> > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
Are
> > there
> > > > any alternatives to this from within SQL Server?
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > > >
> >
> >
> >|||Hi
I guess you could get it changed to produce updategrams and use SQLXML, but
it would be far easier and quicker just to dump a datafile and load it
en-mass.
If you want to still with the SQL statements then you could look at loading
this into a table and then using a cursor and execute statement to run them
(see books online for both), this would rely that each statement was less
than 8000 characters and contained no carriage return or line feeds.
John
"ChrisR" wrote:
> It is made up for me. A mainframe does some stuff, an ETL tool called Tree
> House does some stuff, etc.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FFCD1719-BA31-49DE-BD31-BC52EF3AD898@.microsoft.com...
> > Hi Chris
> >
> > How do you create the file that makes up these SQL Statements?
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > I appreciare your ideas, but Im confused.
> > >
> > > > You could write your own application to parse the file and run each
> > > command
> > >
> > > I really need to do this from within SQL Server.
> > >
> > > > If you just provided a data file and loaded it into a staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > >
> > > How would this be any better than what I have? I still need to get it
> from
> > > the file into a table. The way Im reading this, I need to go from file
> to
> > > table, then from table to table?
> > >
> > >
> > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > > > Hi Chris
> > > >
> > > > You could write your own application to parse the file and run each
> > > command,
> > > > a different alternative would be to load the data into a table and use
> the
> > > > EXEC command to run them. As your statements are in a file, there is a
> > > > security risk. If you just provided a data file and loaded it into a
> > > staging
> > > > table you could then insert/update existing data quite easily on-mass
> > > without
> > > > having to insert/update each row individually. You could use DTS, BULK
> > > INSERT
> > > > or BCP to quickly load the data into a staging table.
> > > >
> > > > John
> > > >
> > > > "ChrisR" wrote:
> > > >
> > > > > SQL2K SP4
> > > > >
> > > > > Howdy all. I have a daily process that gets a .txt file full of
> Inserts/
> > > > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> > > Stored
> > > > > Proc that calls a .bat which contains OSQL and runs the .txt file.
> Are
> > > there
> > > > > any alternatives to this from within SQL Server?
> > > > >
> > > > > TIA, ChrisR
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%230AAwWdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> I appreciare your ideas, but Im confused.
> > You could write your own application to parse the file and run each
> command
> I really need to do this from within SQL Server.
Why?
In any case, your best bet is probably BULK INSERT.
> > If you just provided a data file and loaded it into a staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> How would this be any better than what I have? I still need to get it from
> the file into a table. The way Im reading this, I need to go from file to
> table, then from table to table?
Yes and no. You can go from file->table.
What Chris is suggesting is a staging table (presumably w/o indexes) because
this will make the actual load from the file faster.
It's not necessary, but sometimes can improve performance and overall
maintenance.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:C37FBE46-03DF-4FF8-9F53-35532DECCE64@.microsoft.com...
> > Hi Chris
> >
> > You could write your own application to parse the file and run each
> command,
> > a different alternative would be to load the data into a table and use
the
> > EXEC command to run them. As your statements are in a file, there is a
> > security risk. If you just provided a data file and loaded it into a
> staging
> > table you could then insert/update existing data quite easily on-mass
> without
> > having to insert/update each row individually. You could use DTS, BULK
> INSERT
> > or BCP to quickly load the data into a staging table.
> >
> > John
> >
> > "ChrisR" wrote:
> >
> > > SQL2K SP4
> > >
> > > Howdy all. I have a daily process that gets a .txt file full of
Inserts/
> > > Updates/ Deletes and imports them into a SQL DB every day. I have a
> Stored
> > > Proc that calls a .bat which contains OSQL and runs the .txt file. Are
> there
> > > any alternatives to this from within SQL Server?
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
>