Guide on how to use MS SQL Insert Function

[lawrence]

I am trying to take data from a google sheet. There are multiple rows in the sheet. I need each row to be inserted as a new record into a MS SQL Database Table.

I couldn’t find a guide on how to do this? What is expected in the Column field within the n8n dialogue?
Also how do I handle multiple inserts instead of a single insert?

[nico-kow]

Hey @lawrence
Here a short guide for an example of movies:

this is the data i want to insert in the table movies in the mysql database.

[
  {
    "id": "399366",
    "ratings_imdb_value": "6.7",
    "ratings_rottenTomatoes_value": "49",
    "appended_title": [
      "Movie Title 1"
    ]
  },
  {
    "id": "747188",
    "ratings_imdb_value": "6.6",
    "ratings_rottenTomatoes_value": "75",
    "appended_title": [
      "Movie Title 2"
    ]
  }
]

I created a table in the database with the four columns: id, ratings_imdb_value, ratings_rottenTomatoes_value, movie_title

If I select the table and the mode to map data manually in the mysql node in n8n then you need to select each column and map a value to it. Like this:

If i run the workflow, it is inserting the two movies to my db:

Here you’ll find the workflow:

Hope that helps!

Cheers.

[lawrence]

Thank you very much.
This seems to apply to MySQL and not Microsoft SQL. Is there a guide available for that please?

[MutedJam]

Hi @lawrence, the Microsoft SQL Server node works slightly different, but the basic approach is similar to what @nico-kow has shared for MySQL.

The main difference is that n8n expects a match between your field names in n8n and your column names in SQL Server (meaning you might need to use Rename Keys or the Set node to get these right before your actual Microsoft SQL node), simply because the SQL Server node is a bit older than the current MySQL node.

Here’s a quick example workflow:

Table structure

CREATE TABLE
  users (
    id int IDENTITY(1, 1) NOT NULL,
    created_at datetime NOT NULL DEFAULT (sysutcdatetime()),
    first_name varchar(255) NULL,
    last_name varchar(255) NULL,
    email varchar(255) NULL,
  )
ALTER TABLE
  users
ADD
  CONSTRAINT PK__users__3213E83F24316926 PRIMARY KEY (id)

Workflow

In the Microsoft SQL node I am then specifying the field/column names rather then actual values (like I would in the MySQL node).

Result

This workflow leaves me with data like this in my table:

Hope this helps! Let me know if you have any questions on this