alter table with autoincrement field (1 Viewer)

[dj59]

I am attempting to update an oracle table using MS Access.
One of the fields to be updated is a primary key number field.
I'd like to use the autoincrement field to update that field.

My sql is:

Alter table testAutoNum Add column progid autoincrement (1001,2)

This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.
It is also creating an autonumber and I need it to be a number.

What am I doing wrong?

Thanks.

 

[jdraw]

How is the Oracle field defined? Often Oracle Primary key is based on an Oracle sequence, and does not need to be updated by user. It is automatic updated and inserted in the record when the record is created.

Please tell us more.

More details re Oracle here

 

[dj59]

I am trying to use an append query to insert the data into the oracle table. I get an error that says the primary field can not be null. The oracle table primary field is just a number field....not autonumber; so I need to create the sequence of numbers to insert into it. But I don't want to start with 1, I want to tell it the number to start with and increment by 1 (or 2 or whatever).

 

[jdraw]

You have mentioned both Update and Append??
Have you tried just using a number -if you are working in development--
just pick a number

INSERT INTO ORacleTableName(FLD1, FLD2...,FLDX) VALUES (178,"fld2value",..."fldxValue")

 

[dj59]

I apologize. I'm using an append query in order to insert into the oracle table. Yes, I can put the values in that way except there are 100's of rows, so to do it automatically seems appealing.

Why doesn't the code below recognize the number I want to start with? It starts inserting at 1 incrementing by 1, even though I have start at 1001, increment by 2.

 Alter table testAutoNum
  Add column autonumber counter (1001,2)

and/or how can I make this happen as a 'number' rather than an autonumber so it matches the oracle table.

 

[jdraw]

Your sample works for Access, but Oracle is a different animal. ORACLE SQL does not
mean anything to Access and Access SQL doesn't mean anything to Oracle.

If you are intending to add several records into Oracle and there is no autonumber/sequence PK in Oracle: you could

Create a bunch of records in a table in Access (but not autonumber per se) say 100 records starting with your ID field = 900 and increment each additional record by 1
you will also need the other fields in the record. You haven't said much about them.

Now after having built a table in Access, you could link to the Oracle table (if you haven't already)
Create a query to insert your new records into the linked Oracle table.

You can generate records in access with a small vba For Loop.
eg For i = 900 to 1100 or whatever number of records you need.

 

[dj59]

Thank You.
That is what I will do. Create all the numbers and insert along with the other fields.

 

[jdraw]

That seems easiest.

I had a situation years back where I had some local data(Access) that had to get into Oracle (corporate). Now I had oracle with sequences for ID. I tried Access query with sql to got o Oracle get a sequence, then use that sequence to concoct my Access record, then an Insert to Oracle--very slow. I then tried to go get 100 sequences from Oracle, built all the records in Access, then Insert query --faster.

Later got the 100 sequences built the records in Access,put them out to file system and use Oracle loader or sqlPlus to insert the records --even faster (but a little cumbersome).

Good luck.