Issue when inserting into a table in MS SQL Server

Hi n8n Community
Please help this newbie

I am trying to receive an order via API and write the received data into a table in Microsoft SQL Server.
I couldn’t use insert mode, it didn’t work at all, so I am executing a script instead.
this script works fine because the id is a simple value:

INSERT INTO Customers.Deleted(id)
VALUES(’{{$node["$json[“id”]}}’);

But this script won’t work for inserting line_items array as NVARCHAR into a column
the inserted value for line_items is [object Object],[object Object],[object Object]

INSERT INTO [Orders].[Created]
([id],
[line_items]
)
VALUES
(’{{$json[“id”]}}’,
‘{{$json[“line_items”]}}’
);

Two questions.
1- is there any fix for this script? how can I have the whole array inserted into line_items column?
2- is it possible (for example by using a function) to insert each line item from line items to a second table? I mean ne table for the order header and second table for line items, how can I do this?

Any suggestion or help will be highly appreciated.
Thanks
Mike

Hey @intelioum

As far as I can see, there seems to be a problem with the incoming data you’re getting to your MSSQL node.

Have you tried using a set node in front of it to actually fix the data? This would allow you to use the insert node correctly.

I have a sample workflow that I use for testing and it’s working fine. Please have a look here:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "table": "TestTable",
        "columns": "id,content"
      },
      "name": "Microsoft SQL",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        550,
        300
      ],
      "credentials": {
        "microsoftSql": "Microsoft SQL"
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "content",
              "value": "=Content{{(new Date()).toISOString()}}"
            }
          ],
          "number": [
            {
              "name": "id",
              "value": "={{Math.round(Math.random()*10000)}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    },
    {
      "parameters": {
        "operation": "update",
        "table": "TestTable",
        "columns": "id,content"
      },
      "name": "Microsoft SQL1",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        850,
        300
      ],
      "credentials": {
        "microsoftSql": "Microsoft SQL"
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "content",
              "value": "=UpdatedContent{{(new Date()).toISOString()}}"
            }
          ],
          "number": []
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        700,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT * FROM TestTable WHERE id={{$node[\"Set\"].json[\"id\"]}};"
      },
      "name": "Microsoft SQL2",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        1000,
        300
      ],
      "notesInFlow": true,
      "credentials": {
        "microsoftSql": "Microsoft SQL"
      },
      "notes": "Execute Query (SELECT one)"
    },
    {
      "parameters": {
        "operation": "delete",
        "table": "TestTable"
      },
      "name": "Microsoft SQL3",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        1150,
        300
      ],
      "credentials": {
        "microsoftSql": "Microsoft SQL"
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT * FROM TestTable;"
      },
      "name": "Microsoft SQL4",
      "type": "n8n-nodes-base.microsoftSql",
      "typeVersion": 1,
      "position": [
        1300,
        300
      ],
      "notesInFlow": true,
      "credentials": {
        "microsoftSql": "Microsoft SQL"
      },
      "notes": "Execute Query (SELECT All)"
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Microsoft SQL": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Microsoft SQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Microsoft SQL1": {
      "main": [
        [
          {
            "node": "Microsoft SQL2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Microsoft SQL1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Microsoft SQL2": {
      "main": [
        [
          {
            "node": "Microsoft SQL3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Microsoft SQL3": {
      "main": [
        [
          {
            "node": "Microsoft SQL4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

PS: if you’re not sure what to do, you can copy this JSON, go to n8n, create a new blank workflow and paste it (CTRL + V). You should be able to see it.

I hope this helps!

@krynble

What you mentioned is a very basic SQL usage but this is not what I am facing.

When I am executing the SQL Server node it shows the arrays in the result and this what supposed to be inserted in the table

But when you look at the inserted data this is what I get

It seems that this is a bug in SQL Server node.
it passes the single string but can’t pass an Array to the SQL Table.

Same thing with Postgres works fine with no issue.

What I am trying to do as a work around is to have a SET node or a function to extracts just that array from the whole data and INSERT it to a new table.

SO, basically, how I can split a large array and just transfer a sub-array to the next node?
I think SET is the answer but I can’t get it to work.

@intelioum

Thank you for the feedback! Could you share your table schema (a create table with similar data is enough) and some testing data so I can test and fix?

I saw that the node code (Can be found here) does not work properly with Arrays. This is probably the cause.