SQL Server R odbc does not return SCOPE_IDENTITY after insert

velaa5lx 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(115)

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

sql-server

来源：https://stackoverflow.com/questions/64443390/r-odbc-does-not-return-scope-identity-after-insert

2条答案

按热度按时间

2mbi3lxu1#

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');

赞(0）回复(0）举报 2023-08-02

5fjcxozz2#

could try replacing SCOPE_IDENTITY() used for the current code block with @@identity for the session.

such as:

mainID <- dbGetQuery(conn, "SELECT @@identity AS id")

example of use with DBI::dbAppendTable:

appendTable <- function(){

      main_table <- tibble::tibble(
        datePublished = input$pubDate_in,
        invalidated = FALSE
      )

        tryCatch(
          pool::poolWithTransaction(con, function(conn) {
            DBI::dbAppendTable(conn, DBI::Id(schema = schema, table = "mainTableName"),main_table)

            mainID <- dbGetQuery(conn, "SELECT @@identity AS id")
            bridge_table <- tibble::tibble(
              itemID = NULL,
              otherID = multipleOtherIDs,
              mainID = mainID$id
            )

            DBI::dbAppendTable(conn, DBI::Id(schema = schema, table = "bridgeTableName"),bridge_table)
            diag_rep("Saved action to main table")
            }
          ),
          error = function(e){
            r$errorHandling$action = "Saving an action"
            r$errorHandling$error = e
            r$errorHandling$state = "ERROR"
          }
        )

    }

"The @@IDENTITY system function returns the last identity value generated for any table in the current session and scope. However, it's worth noting that @@IDENTITY can return unexpected results in certain scenarios, particularly if there are triggers on the table that perform additional insertions with identity columns. In such cases, SCOPE_IDENTITY() or OUTPUT INSERTED.column_name may be preferred to ensure the correct identity value is retrieved."

or do it as a stored procedure (likely more efficient) such as here .

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server R odbc does not return SCOPE_IDENTITY after insert

2条答案

相关问题

热门标签

最新问答