Hi and apologies for what I am sure is a dumb newbie question.
I am trying to insert the daily carbon intensity records into a database table one record at a time as the size of the JSON string returned from the API call (HTTP Request) is too long to send to the SQL Server directly.
I cannot find any examples of how to use the SPLIT node to do this although I am sure it can.
I have included the flow export below and have created some dummy code in the SQL Server node as an illustration of what I am trying to achieve.
Can anyone help please?
[
{
"id": "60bb36e8.afff78",
"type": "inject",
"z": "25a64040.bdb25",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 140,
"y": 60,
"wires": [
[
"121ecda9.19be22"
]
]
},
{
"id": "121ecda9.19be22",
"type": "http request",
"z": "25a64040.bdb25",
"name": "Carbon Intensity",
"method": "GET",
"ret": "txt",
"paytoqs": "ignore",
"url": "https://api.carbonintensity.org.uk/intensity/date",
"tls": "",
"persist": false,
"proxy": "",
"authType": "",
"x": 360,
"y": 60,
"wires": [
[
"9c49ecd6.0a88f",
"7f8193d8.17c1ec"
]
]
},
{
"id": "9c49ecd6.0a88f",
"type": "debug",
"z": "25a64040.bdb25",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 630,
"y": 60,
"wires": []
},
{
"id": "7f8193d8.17c1ec",
"type": "json",
"z": "25a64040.bdb25",
"name": "",
"property": "payload",
"action": "",
"pretty": false,
"x": 210,
"y": 160,
"wires": [
[
"be77bb00.cd17e8",
"34c7c8bb.7ad948"
]
]
},
{
"id": "be77bb00.cd17e8",
"type": "debug",
"z": "25a64040.bdb25",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 390,
"y": 260,
"wires": []
},
{
"id": "34c7c8bb.7ad948",
"type": "split",
"z": "25a64040.bdb25",
"name": "",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "key",
"x": 370,
"y": 160,
"wires": [
[
"d93e6d53.5f19f",
"259d6295.38254e"
]
]
},
{
"id": "d93e6d53.5f19f",
"type": "debug",
"z": "25a64040.bdb25",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 630,
"y": 260,
"wires": []
},
{
"id": "212a6e99.85a372",
"type": "debug",
"z": "25a64040.bdb25",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 910,
"y": 160,
"wires": []
},
{
"id": "259d6295.38254e",
"type": "MSSQL",
"z": "25a64040.bdb25",
"mssqlCN": "de349d30.e337f",
"name": "INSERT Carbon Intensity",
"query": "-- DDL and sample data population, start\nDECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,\n [from] DATETIMEOFFSET(3),\n [to] DATETIMEOFFSET(3),\n [forecast] int,\n [actual] int,\n [index] nvarchar(5)\n);\n\n-- DDL and sample data population, end\nDECLARE @data NVARCHAR(MAX) = {{{payload}}}\n \n-- N'{\"data\": [{ \n-- \"from\": \"2021-09-23T23:00Z\", \n-- \"to\": \"2021-09-23T23:30Z\", \n-- \"intensity\": { \n-- \"forecast\": 151, \n-- \"actual\": 143, \n-- \"index\": \"moderate\" \n-- } \n-- }]\n-- }';\n\n-- INSERT INTO @tbl ([from], [to], [forecast], [actual], [index])\n-- VALUES (\n-- @data.from, \n-- @data.to, \n-- @data.intentity.forecast, \n-- @data.intensity.actual, \n-- @data.intensity.index\n-- );\n\n-- test\nSELECT * FROM @tbl;",
"outField": "payload",
"x": 670,
"y": 160,
"wires": [
[
"212a6e99.85a372"
]
]
},
{
"id": "de349d30.e337f",
"type": "MSSQL-CN",
"name": "AZURE SQL",
"server": "andimcsqlserver.database.windows.net",
"encyption": true,
"database": "ANDIMC-SQL"
}
]
