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 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.