Returning @@IDENTITY from an OPENQUERY INSERT Statement

  • RichardB-309825

    SSC Rookie

    Points: 31

    Hi, I'd like to know if it's possible to get the equivalent of and output parameter back from an SQL stored procedure which uses OPENQUERY to insert a record to an Oracle linked server.

    e.g.

    INSERT  OPENQUERY (ORACLE,  

                                 'SELECT

                                 ColumnID,

                                 Value  RECORDCD,

                                 FROM Table WHERE 1=2')

    VALUES  (1, @Value);

    ColumnID is the identity and I'd like to get this value back from the insert if possible (it appears I need to set it to 1 in the insert statement to allow this to work, but it does insert with a new value ok!).

    I'm not sure if this is possible though - can anyone help?

    Thanks

  • dotnet85

    SSC Rookie

    Points: 49

    any update on the topic? I am facing the same issue.
    Thank you

  • Jeff Moden

    SSC Guru

    Points: 993652

    dotnet85 - Tuesday, May 22, 2018 7:51 PM

    any update on the topic? I am facing the same issue.
    Thank you

    I don't know what ORACLE uses as a "last row marker inserted" but it's not likely that OPENQUERY can provide such a return.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    Jeff Moden - Tuesday, May 22, 2018 9:30 PM

    dotnet85 - Tuesday, May 22, 2018 7:51 PM

    any update on the topic? I am facing the same issue.
    Thank you

    I don't know what ORACLE uses as a "last row marker inserted" but it's not likely that OPENQUERY can provide such a return.

    Besides the fact that the insert happened on the remote server, so the local server's @@identity would not be getting updated as a result.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • dotnet85

    SSC Rookie

    Points: 49

    Thank you for your both replies and clarifications.

    But why SQL Server looks like parsing the query provided inside OPENQUERY, though the statement is meant to be executed on the remote server (i.e., Oracle)?

    I have tried different ways, but still getting errors. Here they are:

    using Sequence CURRVAL

    DECLARE @SOMEID BIGINT;DECLARE @QUERY NVARCHAR(MAX) = N'INSERT OPENQUERY(ORACLE_SERVER2, ''SELECT CODE, DESCS, CREATED_BY FROM STORAGE_TYPE'')VALUES(''1234'', ''1234DESC'', ''TESTTEST''); SELECT STORAGE_TYPE_SEQ.CURRVAL INTO @SOMEID FROM DUAL;';EXEC SP_EXECUTESQL @QUERY, N'@SOMEID BIGINT', @SOMEID OUTPUT;SELECT @SOMEID;

    Msg 102, Level 15, State 1, Line 90 Incorrect syntax near '@SOMEID'.

    (1 row(s) affected)


    using RETURNING statement

    DECLARE @SOMEID BIGINT;DECLARE @QUERY NVARCHAR(MAX) =  N'INSERT OPENQUERY(ORACLE_SERVER2, ''SELECT CODE, DESCS, CREATED_BY FROM    STORAGE_TYPE'')VALUES(''1234'', ''1234DESC'', ''TESTTEST'') RETURNING ID INTO @SOMEID';EXEC SP_EXECUTESQL @QUERY, N'@SOMEID BIGINT', @SOMEID OUTPUT;SELECT @SOMEID;

    Msg 102, Level 15, State 1, Line 90 Incorrect syntax near 'RETURNING'.

    (1 row(s) affected)

    Thank you

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply