SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
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?
 New Topic  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

2872 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  
 New 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.03 seconds. Powered By: Snitz Forums 2000