insert into database and get primary key back

8 views (last 30 days)
Robert van Driel
Robert van Driel on 26 Oct 2015
Answered: Sudhanshu Bhatt on 28 Oct 2015
I would like to use the following query to insert data into a database and get the primary key back:
query = INSERT INTO source(timestamp,latitude,longitude,types_id) OUTPUT INSERTED.ID VALUES ('2015-10-14 06:55:41.000','44.0683','-9.5347','13')
curs = fetch(exec(conn, query0));
This sql statement works in Microsoft SQL server Management studio. When Using matlab the data is inserted into the database, but I don't get the primary key back.
The output is generated using curs = fetch(exec(conn, query0)) and curs.Message: 'Invalid Cursor: A result set was generated for update.'
Does anybody has a suggestion.

Answers (1)

Sudhanshu Bhatt
Sudhanshu Bhatt on 28 Oct 2015
Hi Robert van Driel,
I understand that you want to retrieve the ID of the record just inserted using the Database Toolbox commands.
Unfortunately, this functionality is not yet supported by the Database Toolbox.
However, there are two workarounds for retrieving the ID/primary key of the record just inserted.
Workaround 1: You can use "@@IDENTITY" keyword to retrieve the ID of the latest inserted row, for example:
r = exec(conn,'SELECT @@IDENTITY')
r = fetch(r)
Workaround 2: Execute the query on a lower level using:
% Get a low level handle
connectionHandle = conn.Handle;
% Create a new statement
queryStatement = connectionHandle.createStatement;
% executeQuery does allow resultsets as output
resultSet = queryStatement.executeQuery('INSERT INTO table_name(value1, value2,..)")
% Get the first resultset row
resultSet.next;
% Get the first column of the returned resultset
ID = resultSet.getInt(1)
% Clean-up
resultSet.close
queryStatement.close;
If this does not resolve this issue, please create a Technical Support Request by clicking on the link below: Create Technical Support Request

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!