Oracle - SQL 'insert' Question

Hi:

I have a database table with 5 columns - the first column is the PK which increments from 1 to the lastest record (N).

If I want to do an insert of a record to this table - should I physically enter the new record's number in the insert statement? i.e. (N+1)

Or should I use the "seq.nextval" statement?

If I use "seq.nextval" do I just enter "seq.nextval" in the insert statement?

New to DB
Wednesday, December 17, 2003

Does the primary key column is an autonumber column ?
If Yes just do an insert statement without mentioning the PK column. The server will insert the right number for you.

Milton
Wednesday, December 17, 2003

In Oracle, you want to use a sequence in the Insert statement.

Here's a link that discusses the issue>>
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6575961912937

will
Wednesday, December 17, 2003

If you're sure an Oracle sequence is used to generate the primary key for this table, then yes, use seq.nextval in your insert statement.  For example:

insert into customer (id, name) values (customer_id_seq.nextval, 'Jim Bob');

Stress
Wednesday, December 17, 2003

Milton, have you ever actually USED Oracle?

It isn't helpful to give "advice" which has never been tested or tried.

HeWhoMustBeConfused
Wednesday, December 17, 2003

HeWhoMustBeConfused: Milton is answering the question for SQL Server for which his advice is correct. The OP doesn't mention the database but seq.nextval generally means oracle. If you've never used oracle, you probably wouldn't know that and it could be pseudocode stuff. Don't blast someone just because they are answering the question based on their exp.

ko
Wednesday, December 17, 2003

Do you mind if there are gaps in the 1..N?  Using sequence.nextval is likely to produce gaps, because rollbacks will not backtrack the sequence.

If gaps don't matter, use sequence.nextval.  With sequences, Oracle automatically manages the concurrent accesses of the sequence value so you wouldn't have to worry about it.  Otherwise, you'll have to write some code to select the MAX from the table to determine N+1, then you'll have to write some code to manage the database locking errors and initiate retries when two sessions try to simultaneously insert a row into the table.

T. Norman
Wednesday, December 17, 2003

Actually it won't be locking errors that you'll get, it would be a unique key violation.  Locking errors would occur on simultaneous inserts only if you used a separate table to hold the value of N, which you updated after every insert.

T. Norman
Wednesday, December 17, 2003

"The OP doesn't mention the database but seq.nextval generally means oracle."

Actually, the OP did mention it, in the title of the thread.

anon
Wednesday, December 17, 2003

ko:

Huh?  Which part of "Oracle - SQL 'insert' question" fails to mention Oracle, or includes SQL Server?

HeWhoMustBeConfused
Wednesday, December 17, 2003

HeWhoMustBeConfused: my bad. i skip the titles, just read the comment. My apologies to you and all.

ko
Thursday, December 18, 2003

ko:  Mine too ... too many pre-Christmas beers makes for grumpy typing ... *grin*

HeWhoMustBeConfused
Thursday, December 18, 2003

just curious... how do you skip the title? don' tyou have to click on it to read the thread???

_
Thursday, December 18, 2003

yes, but it's only in short term memory as a link, not as a title and in my mind, not really related to the content. And often i find in this forum, the title is only covered by the first or second poster and then everyone moves on to other things.

anyway, that's my excuse and i'm sticking to it :)

ko
Thursday, December 18, 2003

  Recent Topics

  Fog Creek Home