How to get ID into field upon insert (SCOPE_IDENTITY() ?)

I have two fields:

ID - primary key and identity field (seed 1 increment 1)
GroupID - int

Upon INSERT I want groupID to be the value of ID

The only way I can figure out how to do this (being inexperienced with sql) is to INSERT with GroupID = 0 and then do a SELECT @.iGroupID=SCOPE_IDENTITY() and then UPDATE the record. But even inside a transaction this seems error prone and not the right way. Can anyone tell me the correct way to do this. Thank you in advance.You can try using a trigger.

CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[GroupID] [int] NOT NULL ,
[AnotherColumn] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TRIGGER [InsertTrigger] ON [dbo].[Table1]
AFTER INSERT
AS
declare theCursor cursor for SELECT ID FROM inserted
open theCursor
declare @.ThisID int
select @.ThisID = -1
while 1 = 1
begin
fetch next from theCursor into @.ThisID
if @.@.fetch_status <> 0
 break
Update Table1
Set GroupID = @.ThisID
WHERE ID=@.ThisID
end
close theCursor
deallocate theCursor

|||I hope you are using SQL Server 2000 ... If that being the case we can use UDFs to accomblish this task ... A typical code snippet for the same looks like below:

CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END

CREATE TABLE vin_test
(
colAINT IDENTITY(1,1) NOT NULL,
colBINT DEFAULT dbo.GetIdentity(),
colCVARCHAR(10)
)

INSERT INTO vin_test (colC) VALUES ('Test')
SELECT * FROM vin_test|||Yes the ISP is server 2000 and my MSDE seems to support functions but prompts me to select from Inline, Table-values, Scalar-valued, can you please tell me which to select. Also I have about 20 tables I need to do this with. Do I need to write a function for each table?

THANKS!|||Yes, we need to create one for each table. I am sure it is better than creating 10 triggers ... :)

Select the scalar-valued function ...|||My final question:

Is the UDF better/safer/more stable/reliable then what I am doing in the stored procedure where inside a transaction I insert, get the scope_identity and then update the just inserted record. I understand my (hack/kludge?) method may take more server processing but I besides that I am wondering if it is any less reliable then the UDF method. (last question :) again, thanks.|||I can say it with certain that UDFs are quite stable ... There is no second thoughts on that statement. We have used this in our production code and have tested the same with concurrent users ... Works like a gem ... :) ...

Hmm ... the second part of the question has a couple of interesting points to backup:
1. Keep the transaction short. Which means if you have a big routine to do the same it is quite not advisible.
2. CPU cycles are not cheaper and so is memory. If you were to use SPs in such a fashion then we are using both ... :) ... And I dont recommend the same strongly ...

I know this is a delicate topic to answer and can raise a couple of eye-brows who are reading this ... But these are my views on the same. I am open to hear from others if they think otherwise ...|||stored procedures return only integers...UDF can return other datatypes like float...etc
other than that i guess both are similar...in terms of security/performance...etc|||Hmm ... Stored Procedures can also return more than one value using the OUTPUT parameter types ... And I feel that we can do things in UDF that are quite not possible with Stored Procedures ... Read my article on UDF at :http://www.extremeexperts.com/sql/articles/UDFFunctions.aspx ...

And for the present problem description ... I feel UDF is a cleaner and neater way rather than the conventional UDF approach ...