CODEWITHSUNDEEP
iphonesqlsql-servertriggers
Tariq
Tariq

Reputation: 9979

How to Retrieve autoincremnt value after inserting 1 record in single query (sql server)

I am have two fields in my table:

One is Primary key auto increment value and second is text value. lets say: xyzId & xyz

So I can easily insert like this

insert into abcTable(xyz) Values('34')

After performing above query it must insert these information

xyzId=1 & xyz=34

and for retrieving I can retrieve like this

select xyzId from abcTable

But for this I have to write down two operation. Cant I retrieve in single/sub query ?

Thanks

Upvotes: 2

Views: 137

Answers (2)

jlvaquero
jlvaquero

Reputation: 8785

I think you can't do an insert and a select in a single query. You can use a Store Procedures to execute the two instructions as an atomic operation or you can build a query in code with the 2 instructions using ';' (semicolon) as a separator betwen instructions.

Anyway, for select identity values in SQL Server you must check @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT. It's faster and cleaner than a select in the table.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

If you are on SQL Server 2005 or later you can use the output clause to return the auto created id.

Try this:

insert into abcTable(xyz)
output inserted.xyzId
values('34')

Upvotes: 2

Related Questions