Search code examples
rsql-serverodbc

R odbc does not return SCOPE_IDENTITY after insert

I want to insert a new row into a database using R and the odbc package.

The setup is as follow:

  • I use SQL Server to host the database
  • I have setup an ODBC Driver on my machine in order to connect to the database

I created a new table

CREATE TABLE [dbName].[dbSchema].[TestTableName] 
(
    MyID INT IDENTITY(1,1), 
    MyValue VARCHAR(255), 
    PRIMARY KEY (MyID) 
)

As far as I understand the problem it doesn't matter how the table was created.

Now I want to insert a new entry to this table and keep the new auto incremented value of MyID. To this end I can run the following SQL statement:

INSERT INTO [dbName].[dbSchema].[TestTableName] (MyValue)
VALUES ('test'); 

SELECT SCOPE_IDENTITY() as newID;

When I run this statement in SQL Server Management Studio, it happily returns a 1x1 table containing the new ID.

And now my problem starts: when I try to do the same form within R by using:

con <- odbc::dbConnect(
          odbc::odbc(),
          dsn = ...,
          database = "dbName",
          UID = ...,
          PWD = ...
        ) # the connection is tested and works
createQuery <- "INSERT INTO [dbName].[dbSchema].[TestTableName] (MyValue ) VALUES ('test'); SELECT SCOPE_IDENTITY() as newID;"
dbSendRes <- odbc::dbSendQuery(conn = con, statement = createQuery)

The result in dbSendRes is:

<OdbcResult>
  SQL  INSERT INTO [dbName].[dbSchema].[TestTableName] (MyValue ) VALUES ('test'); SELECT SCOPE_IDENTITY() as newID;;
  ROWS Fetched: 0 [complete]
       Changed: 1

Hence, the insert statement is performed successfully on the server, however I do not get the newly assigned ID as a result. The content of dbFetch is an empty data.frame. I also tried using dbSendStatement and dbExecute without any progress. I also tried digging into the additional parameters like immediate=TRUE without any success. I also tried to switch back to the old RODBC package and use the methods therein. Nothing works.

Am I missing something?

Problem remains that I somehow have to keep track on the new ID which is assigned to the db entry! Without that ID I can't proceed. I could do a workaround and query the max ID after complete insert by another separate statement, however this may of course lead to other more fundamental problems if in the meantime another statement would insert another entry...

Happy for any suggestions! Best

Solution

  • Based on zero knowledge of R... To have the INSERT return the newly created id you could add an OUTPUT clause to the INSERT statement. It appears this would work. Something like this

    INSERT INTO [dbName].[dbSchema].[TestTableName] (MyValue ) 
output inserted.MyID
VALUES 
('test');