Microsoft SQL - Get id inserted

I’m using Microsoft SQL node in order to insert data from my Wokflow.

First of all, i try to user Execture Query but this option doesn’t loop. Could you confirm this bug?

Then i use Insert instead of Execture Query. In order to bind my DB data to my Workflow data i user set node. Like that i can set my_value_in_db = MyResultFromNode. Then in my Microsoft SQL node i only put my_value_in_db and it’s working. Is there a way to directly set my_value_in_db = MyResultFromNode in columns of Microsoft SQL node?

At the end, i try to get back my id_just_created so in my Microsoft SQL node columns i set my_value_in_db, id_just_created. Of course, SQL server say that i’m not allow to insert id_just_created. How can i have my id_just_created back?

Many thanks

Hi @michael75,

First of all, i try to user Execture Query but this option doesn’t loop. Could you confirm this bug?

This is the intended behaviour for most database nodes (here’s the list), including the Microsoft SQL Server one. You could split up your execution in batches of 1 and then process each batch individually like so:

Is there a way to directly set my_value_in_db = MyResultFromNode in columns of Microsoft SQL node?

Yes, when executing queries, the node supports expressions. Simply select the node you want to read data from in the expression editor and the required expression is added:

At the end, i try to get back my id_just_created so in my Microsoft SQL node columns i set my_value_in_db, id_just_created. Of course, SQL server say that i’m not allow to insert id_just_created. How can i have my id_just_created back?

I don’t think there is any built-in logic for this. But you could simply select the top(1) rows from the table you have just inserted data into by running two queries like so for each execution of your node:

INSERT INTO users (first_name, last_name) VALUES ('{{$json["first_name"]}}', '{{$json["last_name"]}}');
SELECT TOP(1) * FROM users ORDER BY id DESC;

I’ve also included this into my example from your first question.