Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How can I insert into a table and get p.key back?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

8 Posts

Posted - 12/22/2010 :  09:53:07  Show Profile  Reply with Quote
Hi again.
To explain a question.

If I have a table A (with identity or without):

create table A(
A_id int primary key identity,
anything nvarchar(max)

I need a procedure that will return a table
(B - shown later).. holding only one value ... value of a primary key from the new row inserted in this table (A).

Like this, i insert (in a procedure) this:

insert into A (anything) values ( 'Hello World')

and get back as a result table holding, for example:


or next time I get 2, and so on...

Let's say that that value which is being incremented is in the Table B, like:

create table B(
b_id int primary key,
counter int

that table has only one row:
insert into B (b_id, counter) values ( 0, 0 )

and every time when I insert something into the table A, I read the table B for the only one row there, value of counter, increment it, and put it as a primary key value of A (together with 'anything'), and after that I return table B.

More specificaly, I need this for a, how to say, a Java application, that will send query and expect a resultset back(some data), and what would be in that query is -'call' for a procedure -> which would: insert a new row with some values and return a primary key value (in a table).. so that, later, I could get the same data by holding its primary key value.

(Like for example, I enter name and surname, send it to a server, and get a primary key, with which later I can allways recall the same name and surname.)

What do You think? Is there something shorter or better??

I am very thankfull....

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 12/22/2010 :  10:19:26  Show Profile  Reply with Quote
use scope_identity()

From BOL
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.


Everyday I learn something that somebody else already knew
Go to Top of Page

Starting Member

8 Posts

Posted - 12/22/2010 :  10:48:40  Show Profile  Reply with Quote
Thank You
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000