an insert query with return value
-
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 functionSCOPE_IDENTITY(). So this is what you're after:-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKeyAFAIK 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).
-
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 functionSCOPE_IDENTITY(). So this is what you're after:-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKeyVery 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.
-
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.
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.
-
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.
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).
-
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).
dear friends i used
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKeybut it does not work error is: "Missing semicolon (;) at end of sql statement" what should i do?
-
dear friends i used
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKeybut it does not work error is: "Missing semicolon (;) at end of sql statement" what should i do?
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).
-
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).
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
-
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
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).
-
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).
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 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
: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).
-
dear friends i used
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKeybut it does not work error is: "Missing semicolon (;) at end of sql statement" what should i do?
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.