Posted by Mike Epprecht \(SQL MVP\) on 07/03/05 00:53

TREES & HIERARCHIES IN SQL (Morgan-Kaufmann), 2004 ISBN 1-55860-920-2

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Wolfgang Kreuzer" <wolfgang.m.kreuzer@gmx.de> wrote in message
news:1120335173.ac80700d2cdb088b93b1c46d0ec5f847@teranews...
> Hello Joe,
> thanks for your comments.
>
> Where can I find 'TREES & HIERARCHIES IN SQL'? Do you have an ISBN or
> a unique refeference to look for it?
>
> My programming history is C, VB and Axs 2.0 - I worte an application
> which I migrate to SQL-Server and Axs 2k by using the look and feel of
> my app but starting from the scratch as I want to use the benefits of
> a real RDBMS.
>
> My wording may be wrong, but wording is one point. My approach solving
> the problem of inserting entries, rows or recods or blubbs in a table
> was to to use a single SQL statement, but I was unable to find a way
> to get a reference to an identity field in the same record, but I
> didn't succeed. If there is a way to do so; I would love to kwon how.
>
> My current (set-based) way is:
>
> INSERT INTO dbo.prjProjectStruct (prsProjectId, prsPSPName,
> prsPSPIdent, prsUserIdPrjMan, prsIsActive, prsParentId, CreaDate,
> CreaSessInstId, LastModiDate, LastModiSessInstId)
> SELECT INSERTED.prjProjectId, 'Gesamtprojekt', null,
> dbo.prjSysUser2User.su2uUserId, 1, NULL,
> @dtNow, @iSessInstId, @dtNow, @iSessInstId
> FROM INSERTED INNER JOIN dbo.prjSysUser2User ON
> INSERTED.prjSysUserIdOwner =
> dbo.prjSysUser2User.su2uSysUserId WHERE
> (dbo.prjSysUser2User.su2uIsMainEntry = 1)
>
> --###########################################################
> --### update parent id in inserted rows in prjProjectStruct
> --###########################################################
> UPDATE dbo.prjProjectStruct
> SET prsParentId=prsPrjStructId
> FROM dbo.prjProjectStruct INNER JOIN INSERTED ON
> dbo.prjProjectStruct.prsProjectId = INSERTED.prjProjectId
> WHERE prsParentId IS NULL
>
> I am now fully happy with this solution, but IMHO it isn't to bad. If
> there is a way to to do it easier, I am always open to learn from
> others.
>
> Bye
> On 2 Jul 2005 09:24:21 -0700, "--CELKO--" <jcelko212@earthlink.net>
> wrote:
>
>>Get a copy of TREES & HIERARCHIES IN SQL. You can use the nested sets
>>model and completely avoid all of this procedural code.
>>
>>Then take the time to learn RDBMS, so you will know that a row is not a
>>record and a column is not field and that there is no "last inserted
>>record" because SQL works with entire sets and has no ordering -- those
>>are terms from a sequential file system. No wonder you think in terms
>>of triggers and procedural code.
>>
>>It is soooo much easier to program SQL when you have the right mental
>>models.
>

[Back to original message]