Identify of Currently Inserting Row

I'm working on a project and the question came up: Can an insert statement insert it's own identity in to another field?

The idea is that there would be a hierarchical arrangement of records and that the 'parent' field would point to the ID of the parent record and that the 'top' record for a given tree would just point to itself.

We could of course just leave it null for the parent or come back and insert its开发者_StackOverflow社区 ID later. But I was wondering if there was any SQL Server operation to insert an IDENTITY in to another field as the record is being inserted?

EDIT: Or is there a way to specify the default value for a field to be the value of another field? That would also address the issue.

EDIT: The default field being another field has already been answered it seems, and the answer is no, use a trigger. Of course, that doesn't help the issue of constraints and non-null fields. (http://social.msdn.microsoft.com/Forums/en/sqltools/thread/661d3dc8-b053-47b9-be74-302ffa11bf76)

---

No. For this kind of hierarchy unless you are inserting explicit ID values with SET IDENTITY_INSERT ON you would need to insert the top level first and use SCOPE_IDENTITY or the OUTPUT clause to get the inserted ID s to use for the next level down.

---

To a certain degree you can. Insert allows for OUTPUT clause and the OUTPUT can be redirected INTO a table which can be the same table as the one you're currently inserting into. The following example inserts a parent node but it also automatically inserts a child node which has the parent_id set to the newly generated ID during insert:

create table hierarchy (
    id int identity(1,1) not null primary key, 
    parent_id int null, 
    somefield varchar(100));


insert into hierarchy (parent_id, somefield)
    output inserted.id, 'child'
    into hierarchy (parent_id, somefield)
values (null, 'parent');    

select * from hierarchy;

I don't see any practical use in the end though, this is more of a show hat-trick.