How can I retrieve the output if I execute an INSERT query with an OUTPUT clause with the Database Toolbox?

I am working with Microsoft SQL Server (which uses the Transact-SQL dialect of SQL) and I am executing an INSERT statement like the following:

% conn = database(...)\nsqlquery = 'INSERT INTO myTable (price) OUTPUT (INSERTED.ID) VALUES (3.14)';\ncursor = exec(conn, sqlquery)

But whenever I go to get the returned information (from the OUTPUT clause), I see the following:

 

>> cursor.Data\nans =\n     0\n\n>> cursor.Message\nans =\n    'Invalid Cursor: A result set was generated for update.'

How can I get the data that is returned? Even though this isn't a SELECT query, I should be getting data back.

Answer 1

[MathWorks Support Team]

You should use the 'fetch' command directly with your SQL query, rather than using the 'exec' function. For example:

% conn = database(...)\nsqlquery = 'INSERT INTO myTable (price) OUTPUT (INSERTED.ID) VALUES (3.14)';\nresults = fetch(conn, sqlquery)

After running this code, the "results" variable should contain the information that you expect. Note that the format of "results" depends on the 'DataReturnFormat'. For more information, see the documentation for the 'fetch' function:

https://www.mathworks.com/help/releases/R2021a/database/ug/database.odbc.connection.fetch.html#bt2sx81-4