Getting the ID from an insert

Is there a way using t-sql to get the ID of an inserted row following the
insert?
Example:
@.RowID as int
@.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
Smith','123-456-7890')
SELECT * FROM Contacts WHERE ID = @.RowID
Thanks,
ROnIf ID is an Identity column then you can use SCOPE_IDENTITY().
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
> Is there a way using t-sql to get the ID of an inserted row following the
> insert?
> Example:
> @.RowID as int
> @.RowID = INSERT INTO Contacts (Person,Phone_Number) Values('John
> Smith','123-456-7890')
>
> SELECT * FROM Contacts WHERE ID = @.RowID
>
>
> Thanks,
> ROn
>|||Till the days of SQL Server 7.0 we used to rely on @.@.IDENTITY function. But
in my experience that function isn't that reliable. i.e., @.@.IDENTITY isn't
dependent on the current scope. Even if we have inserted some records in a
different table it would fetch us that identity value ;) I am sure we
wouldn't be interested in that.
From SQL Server 2000 there is a new function by name SCOPE_IDENTITY which
returns the last IDENTITY value produced on a connection and by a statement
in the same scope. So its better to use SCOPE_IDENTITY in our select
statement to retrieve the identity value for the record which we inserted no
w.
Example: SELECT SCOPE_IDENTITY()
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Andrew J. Kelly" wrote:

> If ID is an Identity column then you can use SCOPE_IDENTITY().
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:OJoIDBu7FHA.1000@.tk2msftngp13.phx.gbl...
>
>|||So just to clarify another example might by
INSERT INTO PERSON ( SSN, BIRTHDATE)
VALUES( '123456789', '7/4/1976')
SELECT SCOPE_IDENTITY() AS PERSONID;
Is this correct?|||Contraptor@.gmail.com wrote:
> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
I would personally use an OUTPUT parameter, but the syntax you have is
correct.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes its correct!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Contraptor@.gmail.com" wrote:

> So just to clarify another example might by
> INSERT INTO PERSON ( SSN, BIRTHDATE)
> VALUES( '123456789', '7/4/1976')
> SELECT SCOPE_IDENTITY() AS PERSONID;
> Is this correct?
>sql