IDENTITY value copy in INSERT statement

Hello,
I would like to insert the value of an identity column into an other field
during the same insert statement and not by using a trigger.
Sample Table
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
Sample statements NOT working but to indicate what I would like to do
INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
Is this possible and if yes, could you please inform me how?
Thanks in advance,
RemcoWithout using a trigger (error handling omitted):
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
DECLARE @.ID int
BEGIN TRAN -- these next two data operations should be atomic
INSERT INTO Test(DocumentParentID) VALUES (NULL)
SET @.ID = @.@.IDENTITY
UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
COMMIT
INSERT INTO Test(DocumentParentID) VALUES (@.ID)
I dont know of a way to acheive this inline using identity.
Mr Tea
"Remco" <rembo_r@.hotmail.com> wrote in message
news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>|||I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then use
@.@.identity
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> Without using a trigger (error handling omitted):
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
> DECLARE @.ID int
> BEGIN TRAN -- these next two data operations should be atomic
> INSERT INTO Test(DocumentParentID) VALUES (NULL)
> SET @.ID = @.@.IDENTITY
> UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
> COMMIT
> INSERT INTO Test(DocumentParentID) VALUES (@.ID)
> I dont know of a way to acheive this inline using identity.
> Mr Tea
> "Remco" <rembo_r@.hotmail.com> wrote in message
> news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
field
>|||thanks for the tip :)
Mr Tea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6mVLN1FFHA.3972@.TK2MSFTNGP15.phx.gbl...
>I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then
>use
> @.@.identity
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> field
>|||Why does the document reference itself as its own parent? Typically an
adjacency list hierarchy in a table looks like this:
CREATE TABLE Documents (documentid INTEGER NOT NULL PRIMARY KEY,
parent_documentid INTEGER NULL REFERENCES Documents (documentid))
The root nodes of the tree then have NULL as the parent_documentid. If
you use IDENTITY as the key then will need either a trigger or an
INSERT followed by an UPDATE to populate a self-referencing parent id.
David Portas
SQL Server MVP
--|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||Try using Ident)Seeed()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
"Remco" wrote:

> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>
>|||IDENT_SEED returns seed value, not the identity column value.
If you have a column defined as IDENTITY(1,1), The IDENT_SEED
Function will return 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
> Try using Ident)Seeed()
> as in
>
> INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
>
> "Remco" wrote:
>|||I tested it with a newly created table, and it returned a '1', which was
both the seed and the value to be inserted... <gr>. It's Ident_Current()
That is needed here.
Try using Ident_Current()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Current('Test') + 1)
"Roji. P. Thomas" wrote:

> IDENT_SEED returns seed value, not the identity column value.
> If you have a column defined as IDENTITY(1,1), The IDENT_SEED
> Function will return 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
>
>sql