Return SQL Identity field when performing a SQL Insert Import

Avatar
Jody Ennen

I have an API that is calling a SQL Import to Insert several fields into a row on a table.  Is there any way that I can return the Id field of the inserted row?  The Id field is created as an Identity column.

I have tried the following in the Insert SQL for the Import.

INSERT INTO [TableName] ([Fld1],[Fld2],[Fld3])
     VALUES ('Value1', 'Value2', 'Value3')

SELECT SCOPE_IDENTITY() as NewId

But I am not sure how to (or if I can) access the NewId field in the Import response so that it can be returned by the API.

0

Comments

1 comment
Date Votes
  • Avatar
    Tyler Lamparter
    (Edited )
    Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Jody Ennen I'm not too sure if this works with our database connectors as normally you are specifying your own primary key when inserting your data. In your case, does a primary key get dynamically created upon insertion? You can try to use response mapping here: https://docs.celigo.com/hc/en-us/articles/360054647472-Lookups-and-response-mapping. If that doesn't work, you may need to create a lookup step after the insert to find the row that was inserted and return the primary key.

     

    Additionally, what database are you connecting to? SQL Server, MySQL, Snowflake, etc?

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post