an insert query with return value

reza assar

hi all i have a table with the PrimaryKey of AutoNumber so i want to insert a row and retrieve it`s PrimaryKey !!! by the way my DBMS is MSAccess 2003 can any one help me about this?

Johan Hakkesteegt

You can't really make the insert query return anything else then the number of affected rows. You will have to follow your insert statement with a select statement. Something like:

INSERT INTO Table1 (Field1) VALUES('blablabla')
SELECT MAX(PrimaryKeyField) FROM Table1

My advice is free, and you may get what you paid for.

Tripathi Swati

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] FROM tableexpression;

you can first insert and then can retrive scalar value like below

select max(ColumnName1) as Max_RowNumber from table_name;

I guess this will help you.

Reasons are not Important but Results are Important. http://www.sql4professional.blogspot.com Swati Tripathi

J4amieC

No, no no. Really that is about the worse way you can retrieve the inserted key.

J4amieC

Please ignore both posts above telling you to select the max(field) after your insert. It will be prone to problems if you continue to use this method in a high-usage environment. MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY(). So this is what you're after:

-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey

J4amieC

Second response without a clue.

Luc Pattyn

sometimes several people, several apps are operating on the same database table. At the same time. :~

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Johan Hakkesteegt

Well, they shouldn't. There should be a law against it.

My advice is free, and you may get what you paid for.

Luc Pattyn

AFAIK it needs some clarification, maybe this: and that magic variable exists for every connection (similar to "thread-local storage"), so you can access it reliably as long as you continue to use the same connection. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Johan Hakkesteegt

Very nice. I didn't know about either function. Then again, I haven't had any need for this yet.

J4amieC wrote:

MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().

Just in case I will need it in the future, what is the difference / why is it safer to use SCOPE_IDENTITY() with MS SQL ?

My advice is free, and you may get what you paid for.

riced

Assume two users each having own thread. User1 inserts record then thread is pre-empted before doing the select. User2's thread runs and inserts record. User1's thread restarts and does the select. At this point @@IDENTITY contains the pk of the last record inserted (i.e.User2's record). SCOPE_IDENTITY returns the last pk for the current thread (i.e. User1's thread). At least that's how I think it works. :)

Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.

Luc Pattyn

I don't really know, howver this answer was provided not so long ago: IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. whatever that means... :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

reza assar

dear friends i used

INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey

but it does not work error is: "Missing semicolon (;) at end of sql statement" what should i do?

Luc Pattyn

I've never done such things, however, given the error message, I would adapt the INSERT part so it fits my needs, then insert a semi-colon between the INSERT part and the SELECT part of that SQL snippet; then take a deep breath, hit ENTER, and watch it doing what I instructed. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Mycroft Holmes

The practical issue here is that if you have an insert trigger that creates an identity record the @@Identity will return the ID from the record created by the trigger whereas @Scope_Identity() will return the ID from the record you re inserting in the current procedure. This is just another reason why triggers are EVIL!

Never underestimate the power of human stupidity RAH

Luc Pattyn

can you get the identity without an SP, by issuing two SQL commands separated by a semi-colon, like what the OP seemed trying? :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

Mycroft Holmes

Luc Pattyn wrote:

can you get the identity without an SP

Never having tried it I needed to test this out, the following worked within SSMS. The Identity value should be an output parameter but thats not too difficult.

CREATE TABLE [IDTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TextData] [varchar](50) NULL)

DECLARE @SQL VARCHAR(1000)

SET @SQL = 'INSERT IDTest (TextData)VALUES(''Test1'');SELECT @@IDENTITY as ID'

EXEC (@SQL)

DROP TABLE IDTest

Never underestimate the power of human stupidity RAH

Luc Pattyn

:thumbsup:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read formatted code with indentation, so please use PRE tags for code snippets.

I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).

J4amieC

reza assar wrote:

error is: "Missing semicolon (;) at end of sql statement" what should i do? Quote Selected Text

try reading the error message, then engaging brain. Seriously, if you cant debug the error given the erro message then give up programming now.