identity insert

Hi

I'm a SQL Server Programmer having a first touch with Oracle.

Does anyboady know the equivalent of @.@.IDENTITY in PL-SQL? This is used in SQL Server to get the value of the last inserted identity field.

for exemple:
I have a table 'user' with an identity field 'id_user'.

_______
INSERT user (name, password) VALUES ('climber','pokomoonshine')
SELECT @.@.IDENTITY
_______

Last statement is supposed to give me the value created by my INSERT statement in 'id_user', so you don't have to write SELECT MAX(id_user)...

I didn't find any topics in this forum about it yet.

Any help is welcome.oracle uses sequences instead of identity fields, and sequences are separate from tables

you would use SEQUENCE.NEXTVAL in the insert statement, and then you can use SEQUENCE.CURRVAL (in the same session) for the number that was used

here's a good example --

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);

the above example is from http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/sql_elements6.htm

rudy|||This is validating the information I got.

I learned about Oracle and saw you have to work with those functions and define your ID autoincrement into a trigger wich is a different (but interesting!) approach from SQL Server.

Thanks a lot!

Identity In SQL VS Autonumber In Access

Hi

I wonder if anyone can help with the following:

when using an autonumber in access when you use .addnew you automatically
get the field in an autonumber field i.e.

rs.addnew
jno=autonofld
rs.update

jno will have a value. however in SQL you have to update first and then find
the record (I may be wrong) is there a way to get
the field value before doing the update in SQL Server ?

TIA
SteveNormally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--|||Thanks for the response

I need the Identity value at the time of insertion so I can display a Job
Number to the user as soon as the record is
added. The reason I used the Access example is to illustrate the retrieving
of the value before .update was to show what
I wanted to do (sorry it gave the wrong idea).

I will try the SCOPE_IDENTITY.

Thanks Again
Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Steve

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110275881.484473.10290@.z14g2000cwz.googlegro ups.com...
> Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
> INSERT. Could you explain why you want the IDENTITY value before
> insertion? How do you intend to use the returned value? There are some
> strategies you could use, such as generating a value first and then
> INSERTing it. If you want to use an IDENTITY column though I'm not sure
> what benefit you would gain by knowing the value beforehand.
> Serializing INSERTs isn't recommended because that approach doesn't
> scale well. It shouldn't be necessary with an IDENTITY column anyway.
> --
> David Portas
> SQL Server MVP
> --|||Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Select Max(IdentityField)+1 from TableName

That's not a good thing, since the value you get may not actually be
that value, if another process comes in between.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> forgot to mention which version of
> SQL Server I am using) do you have any other suggestions ?

Use @.@.identity instead. If you have a trigger on the table that inserts
into a secont identity table, @.@.identity will have the value from that
table. This is why scope_identity() is usually recommended, since it
returns the most recently used identity value in the current scope.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, but I have to agree with Erland

Steve
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1110279816.145987.160610@.f14g2000cwb.googlegr oups.com...
> Steve,
> Select Max(IdentityField)+1 from TableName
> Madhivanan|||Thanks

I am going this route, luckily I do not need to use triggers on the table.

Steve

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961380405DD2Yazorman@.127.0.0.1...
> Steve (stevej@.ufrmsa1.uniforum.org.za) writes:
> > SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
> > forgot to mention which version of
> > SQL Server I am using) do you have any other suggestions ?
> Use @.@.identity instead. If you have a trigger on the table that inserts
> into a secont identity table, @.@.identity will have the value from that
> table. This is why scope_identity() is usually recommended, since it
> returns the most recently used identity value in the current scope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

IDENTITY COLUMN QUESTION??

Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.

How can I reset the IDENTITY to start from 1 without re-creating the table?

Please advice. Thanks in advance.

PankajTo my knowledge, you need to use TRUNCATE TABLE tablename to reset the identity value|||You can also use:

DBCC CHECKIDENT (authors, RESEED, 30)

where authors is the tablename and 30 is where you want to restart the IDENTITY. keep in mind that you will need to be aware of any potential conflicts in your data, such as if you are using the IDENTITY column as a primary key.

cs

Identity Column Property SQL SERVER

hi

i export tables from Local to Online Server But some tables have a column with Identity=True

But after export tables that Property is not True How I can change it True With Query Analyzer????/

Would it be possible at all to do a 'create sql scripts' on the database, and use the scripts on the live server to recreate your tables? This should fix your problem.

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2
.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:

> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Tabl
e2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:

> Hi,
> Some thing new
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off throu
gh
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:

>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table
2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:

>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:

> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...[vbcol=seagreen]
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>|||Lines: 24
Organization: Posted via Supernews, http://www.supernews.com
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:10634
On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:

>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
Hugo Kornelis, SQL Server MVP