How to Append mult-rows from REST-API dataset to SQL server via workflow

💬 Workflows
,
1

Hi Retool Technical Expert,

Currently we are using retool sandbox workflow, use the REST API qeury to get the data from SAP ,and transform the json result into array, below is the json result:
{"data":[{"MAT_DOC":"6000000153","DOC_YEAR":"2025","TR_EV_TYPE":"WF","DOC_DATE":"2025-03-20","PSTNG_DATE":"2025-03-20","ENTRY_DATE":"2025-03-20","ENTRY_TIME":"01:40:05","USERNAME":"GP00204984","VER_GR_GI_SLIP":"2","EXPIMP_NO":"","REF_DOC_NO":"","HEADER_TXT":"","REF_DOC_NO_LONG":""}]}
How can we run another query base on SQL server resource to append the given data from prevous query, the transform query name is transform,
We can reference it via {{ transform.data}}, but We don't know how to append the data to the Resource name (SXMIS), SQL SERVER table name is Movement_Data.
Appreciate your help. (Do we have to consider the Loop so we can append row by row)

2

Hi @Adam_Zhu,

You don’t need to loop row-by-row — SQL Server (and Retool workflows) support bulk inserts using an array of objects.

OPENJSON is supported in SQL Server 2016+, and it’s perfect for inserting multiple rows. If your SQL Server is older than 2016, we’ll need to do row-by-row loop.

Here is a template to get you started:

INSERT INTO Movement_Data (
  MAT_DOC,
  DOC_YEAR,
  TR_EV_TYPE,
  DOC_DATE,
  PSTNG_DATE,
  ENTRY_DATE,
  ENTRY_TIME,
  USERNAME,
  VER_GR_GI_SLIP,
  EXPIMP_NO,
  REF_DOC_NO,
  HEADER_TXT,
  REF_DOC_NO_LONG
)
SELECT 
  value->>'MAT_DOC',
  value->>'DOC_YEAR',
  value->>'TR_EV_TYPE',
  value->>'DOC_DATE',
  value->>'PSTNG_DATE',
  value->>'ENTRY_DATE',
  value->>'ENTRY_TIME',
  value->>'USERNAME',
  value->>'VER_GR_GI_SLIP',
  value->>'EXPIMP_NO',
  value->>'REF_DOC_NO',
  value->>'HEADER_TXT',
  value->>'REF_DOC_NO_LONG'
FROM OPENJSON('{{ transform.data | toJSON }}')
WITH (
  MAT_DOC NVARCHAR(50) '$.MAT_DOC',
  DOC_YEAR NVARCHAR(4) '$.DOC_YEAR',
  TR_EV_TYPE NVARCHAR(10) '$.TR_EV_TYPE',
  DOC_DATE DATE '$.DOC_DATE',
  PSTNG_DATE DATE '$.PSTNG_DATE',
  ENTRY_DATE DATE '$.ENTRY_DATE',
  ENTRY_TIME NVARCHAR(10) '$.ENTRY_TIME',
  USERNAME NVARCHAR(50) '$.USERNAME',
  VER_GR_GI_SLIP NVARCHAR(10) '$.VER_GR_GI_SLIP',
  EXPIMP_NO NVARCHAR(50) '$.EXPIMP_NO',
  REF_DOC_NO NVARCHAR(50) '$.REF_DOC_NO',
  HEADER_TXT NVARCHAR(255) '$.HEADER_TXT',
  REF_DOC_NO_LONG NVARCHAR(50) '$.REF_DOC_NO_LONG'
)
1 Like
3

Hi Shawn,

Thanks for your quick response. it works.

2 Likes
4

Hi Adam,
I'm glad it worked for you!