Automation Cloud: Retrieve single record from SQL table

  • 17 April 2023
  • 5 replies

Badge +1

How do you read a single record from a SQL table based on its primary key and store it in an object variable in a workflow.


I can get a collection of 1, but cannot extract the record into an object of the same type as the table.
It only allows me to extract it to a workflow variable. 

I would like the same functionality as for SharePoint: ie ‘Retrieve an item’



Best answer by Garrett 18 April 2023, 19:41

View original

5 replies

Userlevel 6
Badge +16

Hi @lvangemst 

I can see where you are coming from. SP Retrieve an Item (single, zero or one) is easier to work with than SP Query a List (multiple, zero or many). 


Like most object types, you need to use a Loop for Each to retrieve values from a collection (even if your collection has only 1 items)

Inside the Loop, you can assign each column value to a workflow variable.


Userlevel 6
Badge +16

Hi @lvangemst 

Example (Using Azure SQL for database)

  1. Query SQL. Save result into obj_SQL_Query variable
    Using condition ID equals <ID> which returns exactly Zero or 1 record.
  2. Use Loop for each to process obj_SQL_Query → Rows collection.
    Loop will exit if there is ZERO item in the collection
  1.  Inside the Loop for each,
    You should be able to retrieve values similar to SP Retrieve an Item.
    Note: The values in Current item are my example table column names. These values will differ from your table. 


Userlevel 4
Badge +12

Garrett has a nice example of using the loop functionality, which is what I use.

There are some other actions like the sharepoint online - query a list action which support a ‘first item’ option in the output variable.

I’ve raised an idea request last year to add the ‘First item’ support in the output for SQL actions.

If you could upvote the idea that would be greatly appreciated.

Add First Item to SQL On-premises actions and object | Nintex Ideas


Badge +1

@Gavin-Adams I’ve upvoted your idea.

Userlevel 6
Badge +16

@Gavin-Adams I’ve voted for your idea too