|
Variable @@IDENTITY sets to automatic integer after inserting
e.g
INSERT INTO TableName
VALUES blabla,blabla, ..., ...
DECLARE @myID int
SELECT @myID = @@IDENTITY
.... From russia with love ....
|
|
|
|
|
I'll try it.
Many thanks,
Abyss
|
|
|
|
|
You should really use SCOPE_IDENTITY() to be safe. @@IDENTITY returns the last Identity value no matter what the scope. For example,
You have 2 tables, table1 and table2. table1 has a trigger that when a record is inserted, it inserts a record in table2.
INSERT INTO table1
VALUES ...
--the record you inserted into table1
--created the identity of i.e. 1001
--and it fired the insert trigger
--which inserted identity value of
--i.e. 2002 into table2.
DECLARE @myID int
SELECT @myID = @@IDENTITY
@myID will be equal to 2002 (the value from table2) because @@IDENTITY ignores scope
if you use...
SELECT @myID = SCOPE_IDENTITY()
@myID would be equal to 1001 because the trigger is outside the scope of this query/stored proc/whatever.
Jeff Martin
My Blog
|
|
|
|
|
u absolutly right. my answer was not accurate
.... From russia with love ....
|
|
|
|
|
Hi,
Many thanks for help. I'm a C++ programer and the suggested methods for some reason fail.
I try to get the inserted row's ID (primary key) which was inserted to a table.
I tried the following API calls immediately after insert. I use the same m_hStmn for the insert and the select query.
This call is successfull however when I query the returned value using the SQLFetch and SQLGetData the returned SQLINTEGER is always zero!?
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT @@IDENTITY AS 'Ident'"), SQL_NTS)
I tried to use the more save SCOPE_IDENTITY() function, however these API calls fail.
The driver returns (for Access table) undefined function.
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY()"), SQL_NTS)<br />
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY() As [Ident]"), SQL_NTS)
In this case I got from the driver one parameter missing error code:
SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY"), SQL_NTS)
Can anybody explain how can I correctly get the inserted row's ID? Why the above calls fail  ?
Thanks,
Abyss
|
|
|
|
|
Hi
I think I've got something it can help you
on
www.codeproject.com/cs/database/DBManipulationADONET.asp
Huseyin Altindag
|
|
|
|
|
Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right?
Thanks,
Abyss
|
|
|
|
|
Hi
Yes but remember always the new ID generated is greater than the last one you insert.
I'll send you(maybe today) a complete application(C#) with SQLServer solution how to do it
Huseyin
|
|
|
|
|
Thanks in advance.
Have you any idea why the original solution fail with "SELECT SCOPE_IDENTITY()" and "SELECT @@IDENTITY AS 'Ident'"? It is on the MSDN - how to obtain the ID of the inserted record. Is it an Access related problem?
Anyway your solution is great I think that it will work in all cases.
Abyss
|
|
|
|
|
Those features are part of Microsoft SQL Server, not Access/Jet.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
No, it is not a great idea. This only works if it is a single user database AND if the ID has not been reset AND SQL Server decides not to fill in gaps. Do not use select max(ID)...
Jeff Martin
My Blog
|
|
|
|
|
I'm using an Access (MDB) database - via Access/Jet ODBC driver. I suppose that it is a single user database - maybe it is possible to access the database table from different applications at the same time... Will such database fill in gaps (reuse IDs) and when resets the server the ID? Mark told (see the thread) that "SELECT SCOPE_IDENTITY()" are part of SQL server not Access/Jet. Any other idea?
I really appreciate your help 
Thanks,
Abyss
|
|
|
|
|
Sorry, no. I don't use Access.
Jeff Martin
My Blog
|
|
|
|
|
If you are calling this in a separate query from the insert, I know SCOPE_IDENTITY() won't work. I'm not sure about @@IDENTITY. I write my stored procs that do the insert where they get the scope_identity() and return it. On the code side, I just get the return from teh stored proc.
Jeff Martin
My Blog
|
|
|
|
|
Uhh, it is out of my knowledge (I'm rather a novice). How does such stored procedure looks like? Something like this?
INSERT INTO TableXXX (A, B) VALUES (3, 5);<br />
SELECT SCOPE_IDENTITY();
in one call. Is it possible at all?
Thanks,
Abyss
|
|
|
|
|
Basically, yes. It is basically a predefined collection of SQL statements stored on the database. But with Access, you can't use them.
Jeff Martin
My Blog
|
|
|
|
|
I already
have had this problem but it exists yet :
I have this View :
VIEW
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 2
1 | John | 3
2 | Mary | 2
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
__________________________
when I say :
SELECT DISTINCT ID FROM [VIEW]WHERE TypeID IN (1,3)AND ID NOT IN (SELECT ID FROM [VIEW]WHERE TypeID NOT IN (1,3))
I get :
3 , 4
But I Want to have only :
3
I dont know what should i do !
|
|
|
|
|
First off, my name isn't Colin, but I'll try to help you. I'm not sure why you are only wanting 3 out of that query, but I'll try to explain why you are getting the 3 and 4.
The first where clause TypeID in (1, 3) will return all records with a TypeID of 1 or 3, so the following records are there...
1 | John | 1
1 | John | 3
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
The next where clause AND ID NOT IN (SELECT ID FROM [VIEW] WHERE TypeID NOT IN (1, 3))
That will return all records with a TypeID of anything other than 1 or 3, which based on your recordset, will be all TypeIDs of 2. So the AND ID NOT IN [subquery] will result in data that looks like...
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
Combining those two results in both IDs of 3 and 4. I get the idea you are trying to find all IDs that have a TypeID of 1 AND 3, but not 2. So you don't want 4 in the result set since it does not have a record with TypeID of 3. Is that correct?
If so, here is a query that does that (replace test1 with your View name)...
<br />
SELECT DISTINCT ID<br />
FROM test1 <br />
WHERE TypeID IN (1,3)<br />
and ID IN (SELECT ID FROM test1 WHERE TypeID = 1) <br />
and ID IN (SELECT ID FROM test1 WHERE TypeID = 3)<br />
and ID NOT IN (SELECT ID FROM test1 WHERE TypeID = 2)<br />
If that's not what you are after, you'll have to be a little clearer in your question.
Jeff Martin
My Blog
|
|
|
|
|
This is exact problem :
I have a RDBMS and i want to search and get query . This is a database about document archiving. Each document may have several subject
(as subjectID in DocumentView,a View for filtering , quering and searching ).
In the application the end user can select many subject and gets the documents in two ways:
#1 . Documents that their subjects are X1 OR X2 OR ... OR Xn.
In this case, documents that may have one or more subject in the selected subjects.For example if user selects A , B , G , U as selected subjects
he gets document result set that their subjects are A OR B OR G OR U . I pass the IDs of selcted subjects to stored procedure as nvarchar
( select * from DocumentView where subjectID in ( 1,12,14,18 ) ) that 1,12,14,18 are ID of A,B,G,U respectivly.
#2 . Documents that their subjects are X1 AND X2 AND ... AND Xn.
For example if user selects A , B , G , U as selected subjects ,in this case user must recieve documents that their subjects are A AND B AND G AND U only.
|
|
|
|
|
I have this view :
VIEW
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 2
1 | John | 3
2 | Mary | 2
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Joe | 1
__________________________
when I say :
SELECT DISTINCT ID FROM [VIEW]WHERE TypeID IN (1,3)AND ID NOT IN (SELECT ID FROM [VIEW]WHERE TypeID NOT IN (1,3))
I get :
3 , 4
But I Want to have only :
3
I dont know what should i do !
|
|
|
|
|
Put aliases around the view names to make sure the query plan gets constructed properly.
SELECT DISTINCT ID
FROM [VIEW] x
WHERE x.TypeID IN (1,3)
AND x.ID NOT IN ( SELECT ID FROM [VIEW] y WHERE y.TypeID NOT IN (1,3) )
|
|
|
|
|
Any one pls give the definition fo sql stored procedure
or what r stored procedure in sql
what r triggers in sql
and finally
difference betn sql triggers and stored procedures
difference betn function in sql and stored procedure
Patel Neelesh A
|
|
|
|
|
A stored procedure (SP) is just a procedure written in SQL. It takes input parameters and can have a return value, but can also return result sets.
A user defined function (UDF) is also like a procedure but written in SQL, however a UDF is more strict. For instance it can only use deterministic functions. UDFs can be used in other queries in the place of values or tables.
A trigger is a special sort of stored procedure that is run automatically when a specific action occurs, for instance a SELECT, INSERT, UPDATE or DELETE on a table. The trigger can perform additional actions depending on what triggered it.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Sir is it possile for u to give more details on this topic with some example and also advantages of each of
If possible pls give a simple example of triggers pls
Patel Neelesh A
|
|
|
|
|
Colin, I'm glad that you're very helpful but this request sounds suspiciously like someone's homework.
IMHO giving him the short explanation is ok but any more and you might as well be doing his homework for him and that won't really help him further down the track.
Man, I was never this lazy back in my school days.
Edbert
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
