insert into database and get primary key back

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.

Answer 1

[Sudhanshu Bhatt]

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