@@Identity

  • Thread starter Thread starter Scott M.
  • Start date Start date
S

Scott M.

How do I set the @@Identity parameter for data that has been read in from a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.
 
How do I set the @@Identity parameter for data that has been read in from
a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.
Click to expand...


Hi,

Directly use the value of the identity field. I assume that you know the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value during
your update command.

Does this answer your question? Or, then, can you provide your SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
-----------------------------------
 
Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what your
code snippet is designed to do. If I have a simple SELECT like "SELECT *
FROM foo" and "foo" already contains a primary key in the db, would that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the identity
field?

Thanks.



Gaurav Vaish said:
How do I set the @@Identity parameter for data that has been read in from a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.
Click to expand...


Hi,

Directly use the value of the identity field. I assume that you know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value during
your update command.

Does this answer your question? Or, then, can you provide your SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
 
Unless I'm misunderstanding your use of syntax, you are confusing the
@@Identity function with a column with the identity property set.
@@identity is used in the case where you have just inserted a new row
in a table that has an identity column defined and you want to
retrieve the new identity value. All of the "@@" globals are functions
which return some kind of information from the server. So when
selecting from a table with an identity column you simply refer to it
by its column name, like any other column. If you are updating the
row, omit the identity column from the update statement (except in the
WHERE clause, where you refer to it by its column name). For more
information on identity columns and @@identity, see SQL Books Online.

--Mary

Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what your
code snippet is designed to do. If I have a simple SELECT like "SELECT *
FROM foo" and "foo" already contains a primary key in the db, would that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the identity
field?

Thanks.



Gaurav Vaish said:
How do I set the @@Identity parameter for data that has been read in from a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.
Click to expand...


Hi,

Directly use the value of the identity field. I assume that you know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value during
your update command.

Does this answer your question? Or, then, can you provide your SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
Click to expand...
 
My question is: do I need to do something to establish the Identity column
in the first place if the data is coming from an existing database that has
a primary key already set in the db?

How does VB.NET know which column I'm talking about when I use @@Identity?


Mary Chipman said:
Unless I'm misunderstanding your use of syntax, you are confusing the
@@Identity function with a column with the identity property set.
@@identity is used in the case where you have just inserted a new row
in a table that has an identity column defined and you want to
retrieve the new identity value. All of the "@@" globals are functions
which return some kind of information from the server. So when
selecting from a table with an identity column you simply refer to it
by its column name, like any other column. If you are updating the
row, omit the identity column from the update statement (except in the
WHERE clause, where you refer to it by its column name). For more
information on identity columns and @@identity, see SQL Books Online.

--Mary

Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what
your
code snippet is designed to do. If I have a simple SELECT like "SELECT *
FROM foo" and "foo" already contains a primary key in the db, would that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the
identity
field?

Thanks.



Gaurav Vaish said:
How do I set the @@Identity parameter for data that has been read in
from
a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.


Hi,

Directly use the value of the identity field. I assume that you know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value
during
your update command.

Does this answer your question? Or, then, can you provide your SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
Click to expand...
Click to expand...
 
VB.NET (ADO.NET) knows nothing about @@Identity. This SQL Server global
function is used in a query after ADO.NET executes the INSERT via the Update
method. You have to add this SELECT to the INSERT (and UPDATE) Commands. The
DataAdapter configuration wizard can do this for you if you ask nicely. I
wrote an article on handling identity issues that might be useful.
See www.betav.com/articles.htm (MSDN).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Scott M. said:
My question is: do I need to do something to establish the Identity
column in the first place if the data is coming from an existing database
that has a primary key already set in the db?

How does VB.NET know which column I'm talking about when I use @@Identity?


Mary Chipman said:
Unless I'm misunderstanding your use of syntax, you are confusing the
@@Identity function with a column with the identity property set.
@@identity is used in the case where you have just inserted a new row
in a table that has an identity column defined and you want to
retrieve the new identity value. All of the "@@" globals are functions
which return some kind of information from the server. So when
selecting from a table with an identity column you simply refer to it
by its column name, like any other column. If you are updating the
row, omit the identity column from the update statement (except in the
WHERE clause, where you refer to it by its column name). For more
information on identity columns and @@identity, see SQL Books Online.

--Mary

Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what
your
code snippet is designed to do. If I have a simple SELECT like "SELECT *
FROM foo" and "foo" already contains a primary key in the db, would that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the
identity
field?

Thanks.



in
message How do I set the @@Identity parameter for data that has been read in
from
a
database? I have read in data via a command (SELECT statement) and am
trying to build a good UPDATE statement and want to use the @@Identity
field.


Hi,

Directly use the value of the identity field. I assume that you know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value
during
your update command.

Does this answer your question? Or, then, can you provide your
SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
Click to expand...
Click to expand...
 
Thanks Bill, but I think the replies so far have been directed more at
explaining what the @@Identity field is and how one might use it. This is
not my question.

My question is: Do I need to establish what this field is or does
@@Identity implicitly mean the primary key field? Can I just use this in my
SQL statements and SQL will know right away what field I'm talking about?

Thanks.


William (Bill) Vaughn said:
VB.NET (ADO.NET) knows nothing about @@Identity. This SQL Server global
function is used in a query after ADO.NET executes the INSERT via the
Update method. You have to add this SELECT to the INSERT (and UPDATE)
Commands. The DataAdapter configuration wizard can do this for you if you
ask nicely. I wrote an article on handling identity issues that might be
useful.
See www.betav.com/articles.htm (MSDN).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Scott M. said:
My question is: do I need to do something to establish the Identity
column in the first place if the data is coming from an existing database
that has a primary key already set in the db?

How does VB.NET know which column I'm talking about when I use
@@Identity?


Mary Chipman said:
Unless I'm misunderstanding your use of syntax, you are confusing the
@@Identity function with a column with the identity property set.
@@identity is used in the case where you have just inserted a new row
in a table that has an identity column defined and you want to
retrieve the new identity value. All of the "@@" globals are functions
which return some kind of information from the server. So when
selecting from a table with an identity column you simply refer to it
by its column name, like any other column. If you are updating the
row, omit the identity column from the update statement (except in the
WHERE clause, where you refer to it by its column name). For more
information on identity columns and @@identity, see SQL Books Online.

--Mary

Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what
your
code snippet is designed to do. If I have a simple SELECT like "SELECT
*
FROM foo" and "foo" already contains a primary key in the db, would that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the
identity
field?

Thanks.



in
message How do I set the @@Identity parameter for data that has been read in
from
a
database? I have read in data via a command (SELECT statement) and
am
trying to build a good UPDATE statement and want to use the
@@Identity
field.


Hi,

Directly use the value of the identity field. I assume that you
know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value
during
your update command.

Does this answer your question? Or, then, can you provide your
SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
Click to expand...
Click to expand...
 
An Integer column can be designated as containing an auto-incrementing
"identity" value managed by the server (or ADO). After you add a new row,
the system-generated value (in simple situations) can be returned by
executing a SELECT @@Identity or (in more sophisticated systems by executing
a SELECT against the SCOPE_IDENTITY( ) function (which I prefer). This
column is usually reserved for use as a primary key for the row as it's
guaranteed to be unique within the scope of the local table. Using Identity
in this way works fine unless your database is spread over several servers
and you need to provide a unique identifier "globally". In this case we
suggest you use a GUID as the PK.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Scott M. said:
Thanks Bill, but I think the replies so far have been directed more at
explaining what the @@Identity field is and how one might use it. This is
not my question.

My question is: Do I need to establish what this field is or does
@@Identity implicitly mean the primary key field? Can I just use this in
my SQL statements and SQL will know right away what field I'm talking
about?

Thanks.


William (Bill) Vaughn said:
VB.NET (ADO.NET) knows nothing about @@Identity. This SQL Server global
function is used in a query after ADO.NET executes the INSERT via the
Update method. You have to add this SELECT to the INSERT (and UPDATE)
Commands. The DataAdapter configuration wizard can do this for you if you
ask nicely. I wrote an article on handling identity issues that might be
useful.
See www.betav.com/articles.htm (MSDN).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Scott M. said:
My question is: do I need to do something to establish the Identity
column in the first place if the data is coming from an existing
database that has a primary key already set in the db?

How does VB.NET know which column I'm talking about when I use
@@Identity?


Unless I'm misunderstanding your use of syntax, you are confusing the
@@Identity function with a column with the identity property set.
@@identity is used in the case where you have just inserted a new row
in a table that has an identity column defined and you want to
retrieve the new identity value. All of the "@@" globals are functions
which return some kind of information from the server. So when
selecting from a table with an identity column you simply refer to it
by its column name, like any other column. If you are updating the
row, omit the identity column from the update statement (except in the
WHERE clause, where you refer to it by its column name). For more
information on identity columns and @@identity, see SQL Books Online.

--Mary

Thanks for your reply. I am using VB.NET, not C#, so I'm not sure what
your
code snippet is designed to do. If I have a simple SELECT like "SELECT
*
FROM foo" and "foo" already contains a primary key in the db, would
that
field automatically be the @@Identity field when it is brought into my
DataSet? If not, what would I do to mark the correct column as the
identity
field?

Thanks.



in
message How do I set the @@Identity parameter for data that has been read in
from
a
database? I have read in data via a command (SELECT statement) and
am
trying to build a good UPDATE statement and want to use the
@@Identity
field.


Hi,

Directly use the value of the identity field. I assume that you
know
the
name of the field. For example, if you have a table like:

t_sample {
id as autonumber,
first as varchar(50),
second as int
}

Doing a select that includes "id" as a field will give you the
@@Identity (the autonumber). You can use this (columns["id"]) value
during
your update command.

Does this answer your question? Or, then, can you provide your
SELECT
and UPDATE statements? That may things more visible!

--

Happy Hacking,
Gaurav Vaish
http://www.mastergaurav.org
Click to expand...
Click to expand...
Click to expand...
 
My question is: Do I need to establish what this field is or does
@@Identity implicitly mean the primary key field? Can I just use this in my
SQL statements and SQL will know right away what field I'm talking about?
Click to expand...

@@identity is not a field, and does not have anything to do with
designating a primary key, it just retrieves a new column value from
an inserted row that has an identity column. Best practice is to NOT
use it in your SQL statements, use scope_identity instead, as Bill
recommended, because @@identity is not limited to a specific scope.
That way SQL Server will always return the correct value for the
specific table you are inserting into, and not some other table (which
could happen if triggers are defined, etc). For more information, see
SQL Books Online, an updated copy of which is available at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.

--Mary
 
Hi Mary,

Thanks for your replies. You have indicated that you can refer to the field
that serves as the @@Identity field by just using the column name and you
have indicated that @@Identity should NOT be used.

I am confused by these statements for 2 reasons:

1. If I don't know the name of the field that is the @@Identity field, I
can't very well refer to it by name. This was really the whole point of my
original question. Is the @@Identity field in the database automatically
returned by using the @@Identity SQL function or is there something I must
do to the field in the db to identify it as the @@Identity field?

2. I have found that using the DataAdapter configuration wizard, INSERT and
UPDATE commands are generated automatically that include WHERE clauses that
use @@Identity, yet you say not to use it?
 
Your questions would be answered in full if you would just read the
relevant topics in SQL BOL, as I have repeatedly suggested that you
do. To repeat, @@Identity is NOT a field, it's a function, and NO you
should not use it, you should use scope_identity instead as Bill
recommended. SQL Server will handle the rest. The wizards don't always
incorporate best practices, so forget about them and learn to write
(and understand) code on your own by reading the appropriate
documentation.

--Mary
 
Wow Mary, thanks for the attitude!

I don't know why you keep telling me that @@Identity it not technically a
field. I know that and it is beside the point of my question. It does
return a field and it doesn't take a rocket scientist to figure that out
what I meant in my OP.

Your frustration level could have been lowered quite a bit if you had just
READ my OP and instead of giving me a dissertation on SQL, you could have
answered simply by saying that there is nothing in VB code you must do to
get back the @@Identity field of the DB. That's all I was looking for.

The last time I checked, it was appropriate to ask questions and follow ups
here. Did the rules change? Am I now required to sift through lots of
documentation that is not really about what I'm looking for, when I could
have asked a simple question and found someone (obviously, not you) to give
a simple answer?

I suggest you do some reading of your own: "How to win friends and
influence people".
 
Wow Mary, thanks for the attitude!

I don't know why you keep telling me that @@Identity it not technically a
field. I know that and it is beside the point of my question. It does
return a field and it doesn't take a rocket scientist to figure that out
what I meant in my OP.

Your frustration level could have been lowered quite a bit if you had just
READ my OP and instead of giving me a dissertation on SQL, you could have
answered simply by saying that there is nothing in VB code you must do to
get back the @@Identity field of the DB. That's all I was looking for.

The last time I checked, it was appropriate to ask questions and follow ups
here. Did the rules change? Am I now required to sift through lots of
documentation that is not really about what I'm looking for, when I could
have asked a simple question and found someone (obviously, not you) to give
a simple answer?

I suggest you do some reading of your own: "How to win friends and
influence people".
 
The @@Identity global variable is not a column and not a field.
When queried (as it states in the doc and the responses Mary and I have
given) it returns the last generated autoincrement in the current scope. If
you add data to five tables in a query, @@Identity returns the last
autoincrement generated (for the 5th table)--assuming all have integer
columns designated as autoincrement.

Mary Chipman is one of the most respected voluntary contributors to this
list. She is highly experienced and a valuable resource. I would show her
the respect she is due.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
And, she would have gotten it if she didn't give me such attitude. So, far
she has not done much to earn my respect.
 
Scott M. said:
Wow Mary, thanks for the attitude!

I don't know why you keep telling me that @@Identity it not technically a
field. I know that and it is beside the point of my question. It does
return a field and it doesn't take a rocket scientist to figure that out
what I meant in my OP.
Click to expand...

Scott - I have read the above statement from your OP - and I have read it
several times sincethis thread has started, and I must say "your OP makes no
sense". There is no such thing as an @@Identity parameter - so I would walk
away from the OP and try again.

roy fine
 
Sorry Roy, but there is a SQL function called @@Identity. It can be used to
return the value of a auto incremement field in SQL Server. My question was
if there was something I must do in my .NET code to determine what this
value is. The responses to my OP have indicated that this value is
determined from within SQL and that nothing is required in the .NET code to
establish this value.
 
Scott,

read your orignal post - I said there is no such thing as an @@Identity
parameter - that was what you were asking how to set. Your original post
was nonsensical! your your own sake (and professional reputation) - one
would have to strongly advise you against asking people to go back and read
it.
roy
 
Roy, I asked about @@Identity because I didn't know about it. If I misused
the term "parameter", it was because I lacked the information I was looking
for in the first place.

The OP was not, as you say, "non-sensical". The other replies seemed to
have gotten what I was asking. Might I suggest that you not reply at all if
you don't understand the post?
 
Scott, you obviously are an idiot.
But, I will attempt to answer your LAST post because it is the only one that
makes sense.

There are now 4 highly skilled and very experienced people that did not
understand anything you asked in your OP, so I guess all of them (us) are
idiots.

A SQL server database table can have one integer field designated as an
identity field. This value will auto increment when a new row is inserted
into a table that has an identity field. The starting values, as well as
the number that is incremented, can be defined by the user.

If you are inserting a row with your ADO.Net code that will increment the
identity value, and need to reliably retrieve this value, you can,
immediately after the INSERT statement, run "SELECT @@Identity". This will
return an integer that is the LAST IDENTITY VALUE inserted into a table.

As Bill pointed out, there are issues when using the GLOBAL SQL VARIABLE
@@identity.

Assume you have 2 users that are both inserting a record in the same table.
User A inserts record 10, user B inserts record 11. Unless your code has
perfect timing, "SELECT @@identity" will return 11.
In a much worse case, assume there are 10 users inserting records into 10
tables with identity columns defined. In that case, who knows what value
you may get back from "SELECT @@identity"

You will be better served by using "SELECT SCOPE_IDENTITY()" in your code.
The value returned by this FUNCTION is the identity specific to the CURRENT
SCOPE.

As Mary also said, READ BOOKS ONLINE. This will make things much clearer.
Unless you do not really know how to read.

Good Luck
Michael L John
 
Back
Top