Return value after insertion - MonetDB/SQL 2003

Question

I am using MonetDB (SQL:2003) and want to get the last inserted row and tried to do the query with OUTPUT. However it failed with syntax error.

Query: INSERT INTO MyTable(name, age) OUTPUT INSERTED.* VALUES ('John Doe', 25)

Is there any alternative solution without using another SELECT query?

Did you try scope_identity()? - should return last ID value entered to a table — Daniel Marcus, Apr 23 '18 at 15:27
I have tried both scope_identity() and @@identity but also failed. (Error: No such operator) — harrison, Apr 23 '18 at 15:32
@harrison . . . You are trying constructs from SQL Server. I don't think MonetDB supports them. — Gordon Linoff, Apr 23 '18 at 15:41
@GordonLinoff Thanks for the clarification. My final solution is selecting the max id from the table. — harrison, Apr 24 '18 at 09:27
@harrison . . . That is quite dangerous in a multi-threaded environment. But it might be necessary. — Gordon Linoff, Apr 24 '18 at 12:25

Fabian Pijcke · Answer 1 · 2018-05-11T10:20:15.793

I usually use a sequence in this situation.

Suppose you have an auto-incremented column in your table. I.e., you created your table with something like:

CREATE SEQUENCE "s_MyTable" AS INTEGER START WITH 1;

CREATE TABLE MyTable (
  id INT DEFAULT NEXT VALUE FOR "s_MyTable",
  name VARCHAR(42),
  age INTEGER
);

Then you can do the following in a transaction:

INSERT INTO MyTable (name, age) VALUES ('John Doe', 25);
SELECT * FROM MyTable WHERE id = get_value_for('s_MyTable') - 1;

I expect get_value_for to return the last value updated by the transaction regardless of any update done by another transaction (as in PostgreSQL). However I could not find this information anywhere so you should check if you plan to use this in a very concurrent environment.

Maybe a smarter way is to do it the other way around: Firstly get an identifier, then do your insert using that id, then retrieve information about that id. That way you do not even need transactions:

SELECT next_value_for('s_MyTable');
-- Keep that id somewhere, let's say you can use it with $id.


INSERT INTO MyTable (id, name, age) VALUES ($id, 'John Doe', 25);


SELECT * FROM MyTable WHERE id = $id;

I don't know of any means to do this with a single statement.

Return value after insertion - MonetDB/SQL 2003

1 Answers1