Autonumeric: Get the last one inserted in Oracle, MySQL, and SQL Server

Hello friends!!! In today’s post we will work with autonumerics in Oracle, MySQL and SQL Server. We will see how to get the last value of the fields IDENTITY (SQL SERVER), AUTO_INCREMENT (MySQL) and IDENTITY (ORACLE from version 12c. Sequence in previous versions).

Autonumeric: How to get the last inserted in Oracle, MySQL, and SQL Server | gpsos.es

If you have in mind to make a select MAX (…) of the field to consult… DON’T DO IT! Not a good idea. The problem is that if you try in your test team it will work well for you, but in reality it will not work for you.

Why is it not reliable to get the autonumerics with Select MAX?

The reason is that in environments with many simultaneous users, between you entering your order in the table and making the query to see the maximum ID, another user may have entered another order in the same table that will already have a higher ID. If you simply find out the maximum you run the risk (almost certainly) that your order lines and your customer will be associated with an order that does not correspond to them. Do you see the problem?

Let’s see how to get the autonumeric values in Oracle, MySQL and SQL Server

SQL SERVER:

SELECT SCOPE_IDENTITY()

Returns the last identity value inserted into a identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. therefore if two instructions are in the same stored procedure, function, or batch, are in the same scope.

MYSQL:

SELECT LAST_INSERT_ID()

in the case of mysql this statement returns the last auto-numeric that has been generated in the same connection that we are using currently. As long as we do not share connections (something unlikely in any case) we would have no problem obtaining it with confidence.

ORACLE:

In Oracle’s case, it’s a bit more complex. Begin with and incredibly, Oracle did not have self-incremental fields until the appearance of its version 12c in June 2013. Before that you had to manage using Sequences and triggers to achieve the same thing.

 CREATE TABLE Orders (
 IDOrder INT PRIMARY KEY,
  ....
 ); 

We also have to create a sequence of the order id

CREATE SEQUENCE SequenceOrders
MINVALUE 1 MAXVALUE 1000 INCREMENT BY 1;

And a trigger that triggers the sequence.

CREATE OR REPLACE TRIGGER orders_bit
 BEFORE INSERT ON Orders
 FOR EACH ROW
 BEGIN
 SELECT SequenceOrders.NEXTVAL
 INTO :new. Order ID
 FROM dual;
 NDT;
 As a step 

As a final step, we had to perform a returning to insert

 INSERT INTO Orders (CustomerID, EmployeeID, OrderDate....)
 VALUES (1, 1...)
 RETURNING OrderID INTO :last_insert_id 

In version 12c they have improved it and the sentence of creation of the incrementable auto is :

 CREATE TABLE Orders
 (OrderID NUMBER GENERATED ALWAYS AS IDENTITY, ... ); 

Now, of course, to obtain the value we must do exactly the same as before in the insertion instruction, that is, use RETURNING INTO to obtain the value, as we have just seen. Learn more at https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/creating-tables-identity-column.html

We hope you found it useful friends! See you in the next one, if you need help with this or another database topic, do not hesitate to contact us.

Comments are closed.