CODEWITHSUNDEEP
c#sqlsql-serveridentity
user2150696
user2150696

Reputation: 9

Query on Identity Column on MS SQL DB

I have created table in MS SQL 2008 with one identity column(Start Value - 1 and Increment is also 1) and 4 other columns. I am accessing this DB from C# ASP.NET. Used to push data only for the non identity column. Identity column will auto increment itself.

As of now i am manually querying the column value with the remaining for columns. But I am facing problem if all the other four column values are equal i am not getting the exact value which i am looking for

Now my query is, Is there any why in C# where I can get the value of the newly created identity column whenever new record is created.

Thanks.

Upvotes: 0

Views: 205

Answers (3)

Kahn
Kahn

Reputation: 1660

In SQL terms you can output the records back if you wish it. But how you might apply this to C# is up to you. Example:

INSERT INTO TABLE_A (SOMETHING, SOMETHINGELSE, RANDOMVAL3)
OUTPUT inserted.A_ID, inserted.SOMETHING, inserted.SOMETHINGELSE, inserted.RANDOMVAL3
SELECT 'ASD','DOSD', 123

But unless you're using merge, you can't use OUTPUT to print out any values from joining tables from an INSERT. But that's another matter entirely, I think.

Also, it's hardly good practice to bounce this data between the application and the DB all the time, so I'd look to alternatives if possible.

Upvotes: 0

Brian P
Brian P

Reputation: 1587

The answer to your question actually lies in SQL Server. You can run:

SELECT @@identity

after your insert to get the last inserted row identity.

http://technet.microsoft.com/en-us/library/aa933167(v=sql.80).aspx

EDIT BASED ON COMMENTS:

Consider using SCOPE_IDENTITY() as referenced here:

http://technet.microsoft.com/en-us/library/aa259185(v=sql.80).aspx

Upvotes: 2

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

You can use 

SCOPE_IDENTITY()

Which will returns the primary key value of the recently inserted row

Upvotes: 2

Related Questions