We help IT Professionals succeed at work.

SQL statement

nats042897
nats042897 asked
on
Medium Priority
252 Views
Last Modified: 2010-04-16
I have an Ms SQL table
Table1
------
ID (Counter, auto-increment) (UNIQUE)
Field1  varchar(50) (NOT UNIQUE)

In my java code, I run a statement like
executeUpdate("INSERT INTO Table1...").
I want to be abble to retreive the value "ID" if the counter
just created in SQL. What's the BEST way? (no Requery and MoveLast please...)
Thanks
Comment
Watch Question

Have you tried Inserting into two tables?

Make the second table contain the ID of the last element and the IP of the user that updated it. (Use this last value as an index to delete the record once you are done with it).

For instance:

executeUpdate("INSERT INTO Table1, Table2..., Table2 =Table1.ID").



Commented:
The answer is dependant on the DBMS that you are using.

In MS SQL Server, you can "SELECT @@IDENTITY".  This will give you the last value auto-inserted into an identity column in the current session.

In Informix:  The value can be obtained with
DBINFO('sqlca.sqlerrd1')  
   LET  p_newformkey = DBINFO('sqlca.sqlerrd1');


Oracle: You can select from the SEQUENCE object first, and then insert that value into the table.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.