Insert Table

Inserts table data into a MySQL database table. This node is optimized for bulk inserts of structured data.

Common Properties

• Name - The custom name of the node.
• Color - The custom color of the node.
• Delay Before (sec) - Waits in seconds before executing the node.
• Delay After (sec) - Waits in seconds after executing node.
• Continue On Error - Automation will continue regardless of any error. The default value is false.

info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs

• Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
• Transaction Id - The unique identifier of a transaction (optional). Use when the insert is part of a database transaction.
• Table Name - The name of the database table to insert data into.
• Table Data - The table data to insert, with columns and rows in table structure format.

Options

• Replace - When enabled, updates existing rows on duplicate key instead of failing. If a row with the same unique key exists, it will be updated with the new values.
• Credentials - MySQL database credentials (optional). Use this if you want to insert without a Connect node.
• Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output

• None - The data is inserted into the database, but no results are returned.

How It Works

The Insert Table node inserts structured table data into a MySQL table. When executed, the node:

1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
2. Optionally retrieves the transaction if a transaction ID is provided
3. Validates the table name and table data
4. For each row in the table data:
   • Constructs an INSERT statement based on the columns
   • Adds column values as parameters to prevent SQL injection
   • Executes the INSERT (or INSERT ON DUPLICATE KEY UPDATE if Replace is enabled)
5. Applies all changes to the database

Requirements

• Either: A valid connection ID from Connect node OR valid database credentials
• Valid table name that exists in the database
• Table data with at least one row
• Table columns must match database table columns
• Appropriate INSERT permission on the table

Error Handling

The node will return specific errors in the following cases:

• ErrInvalidArg - Table name is empty or table data is empty
• ErrNotFound - Connection ID not found
• ErrRuntime - Insert operation failed (table doesn't exist, column mismatch, constraint violation, etc.)

Table Data Format

The table data must be in the following structure:

{
  "columns": ["Name", "Email", "City", "Age"],
  "rows": [
    {"Name": "John Doe", "Email": "john@example.com", "City": "New York", "Age": 30},
    {"Name": "Jane Smith", "Email": "jane@example.com", "City": "Los Angeles", "Age": 28},
    {"Name": "Bob Johnson", "Email": "bob@example.com", "City": "Chicago", "Age": 35}
  ]
}

Usage Examples

Insert from CSV

Read CSV → Insert Table

The CSV node outputs data in table format, which can be directly used:

// CSV output is automatically in table format
$item.table = {
  columns: ["ProductName", "Category", "Price", "Stock"],
  rows: [...]
}

Insert from Excel

Excel Read Range → Insert Table

Excel nodes also output table format:

// Excel range data
$item.range = {
  columns: ["CustomerName", "Email", "Phone", "City"],
  rows: [...]
}

Insert from Web Scraping

Browser Scrape Table → Transform Data → Insert Table

// Transform scraped data to table format
const table = {
  columns: Object.keys($item.scrapedData[0]),
  rows: $item.scrapedData
};

Insert from API Response

HTTP Request → Transform to Table → Insert Table

// Convert API JSON array to table format
const apiData = $item.response.data;
const table = {
  columns: ["OrderID", "CustomerID", "Total", "Status"],
  rows: apiData.map(order => ({
    OrderID: order.id,
    CustomerID: order.customer_id,
    Total: order.total,
    Status: order.status
  }))
};

Common Use Cases

1. Bulk Data Import - Import data from CSV, Excel, or external APIs
2. Web Scraping Storage - Store scraped data in MySQL
3. Data Migration - Move data between systems
4. ETL Operations - Extract, transform, and load data into MySQL
5. Report Data Storage - Save generated report data
6. Batch Processing - Insert multiple records efficiently
7. Sync Operations - Synchronize data from external sources

Replace Mode (ON DUPLICATE KEY UPDATE)

When Replace option is enabled, the node generates INSERT ... ON DUPLICATE KEY UPDATE statements:

INSERT INTO tablename (Name, Email, City)
VALUES ('John', 'john@example.com', 'NYC')
ON DUPLICATE KEY UPDATE
  Name = VALUES(Name),
  Email = VALUES(Email),
  City = VALUES(City)

This is useful for:

• Upserting data (insert if not exists, update if exists)
• Syncing data from external sources
• Updating records based on unique keys
• Avoiding duplicate entry errors

Requirements for Replace Mode:

• Table must have a PRIMARY KEY or UNIQUE constraint
• The constraint determines which records are considered duplicates

Tips for Effective Use

• Table Format - Ensure data is in proper table format with columns and rows
• Column Matching - Table data columns must match database table columns (case-sensitive)
• Data Types - Ensure data types match (numbers as numbers, dates as proper format)
• Batch Processing - For very large datasets, split into batches to avoid timeouts
• Replace Mode - Use Replace option to handle duplicates gracefully
• Transactions - Use transactions for atomic bulk inserts
• Direct Credentials - Use optional Credentials field to insert without Connect node
• Validation - Validate data before inserting to avoid runtime errors

Working with Different Data Sources

CSV to MySQL

Read CSV (file: data.csv)
  ↓
Insert Table
  - Table Name: imported_data
  - Table Data: $item.table

Excel to MySQL

Excel Open (file: data.xlsx)
  ↓
Excel Read Range (range: A1:D100)
  ↓
Insert Table
  - Table Name: excel_import
  - Table Data: $item.range

JSON Array to MySQL

// In JavaScript node: transform JSON to table format
const jsonData = $item.apiResponse;

const table = {
  columns: Object.keys(jsonData[0]),
  rows: jsonData
};

return { table };

JavaScript → Insert Table (Table Data: $item.table)

Query to Insert (Copy Data)

Query (SELECT * FROM source_table)
  ↓
Insert Table
  - Table Name: destination_table
  - Table Data: $item.result

Best Practices

Validate Data First

// JavaScript node before Insert
const rows = $item.table.rows;

// Validate required fields
const validRows = rows.filter(row => {
  return row.Email && row.Name && row.Email.includes('@');
});

// Remove duplicates
const uniqueRows = Array.from(
  new Map(validRows.map(row => [row.Email, row])).values()
);

return {
  table: {
    columns: $item.table.columns,
    rows: uniqueRows
  }
};

Handle Large Datasets

For large datasets (10,000+ rows), process in batches:

// Split into batches of 1000
const batchSize = 1000;
const allRows = $item.table.rows;

for (let i = 0; i < allRows.length; i += batchSize) {
  const batchRows = allRows.slice(i, i + batchSize);

  // Send each batch to Insert Table node
  // Use Loop or recursive flow
}

Use Transactions for Consistency

Connect
  ↓
Start Transaction
  ↓
Insert Table (batch 1)
  ↓
Insert Table (batch 2)
  ↓
Insert Table (batch 3)
  ↓
Commit (all or nothing)
  ↓
Disconnect

Map Column Names

If source columns don't match database columns:

// Map column names
const table = {
  columns: ["CustomerName", "EmailAddress", "PhoneNumber"],
  rows: $item.sourceData.rows.map(row => ({
    CustomerName: row.Name,
    EmailAddress: row.Email,
    PhoneNumber: row.Phone
  }))
};

Performance Optimization

Disable Indexes During Bulk Insert

For very large imports:

Non Query: ALTER TABLE tablename DISABLE KEYS
  ↓
Insert Table (bulk data)
  ↓
Non Query: ALTER TABLE tablename ENABLE KEYS

Use Batch Inserts

The Insert Table node automatically uses parameterized inserts for each row, which is efficient. For maximum performance:

• Insert in batches of 1000-5000 rows
• Use transactions to group batches
• Consider LOAD DATA INFILE for extremely large datasets (use Non Query node)

Connection Reuse

Connect (once)
  ↓
Insert Table (multiple calls in loop)
  ↓
Disconnect

Better than connecting for each insert.

Common Errors and Solutions

Insert Operation Failed

Error: ErrRuntime: Insert operation failed

Common Causes:

• Table doesn't exist
• Column name mismatch
• Data type mismatch
• Constraint violation (NOT NULL, FOREIGN KEY, UNIQUE)
• Permission denied

Solutions:

• Verify table exists: SHOW TABLES LIKE 'tablename'
• Check column names: DESCRIBE tablename
• Validate data types match column definitions
• Ensure required (NOT NULL) columns have values
• Check foreign key references exist
• Verify user has INSERT permission

Table Name Cannot Be Empty

Error: ErrInvalidArg: Table name cannot be empty

Solutions:

• Provide the table name in the Table Name field
• Ensure the variable containing the table name has a value

Table Data Cannot Be Empty

Error: ErrInvalidArg: Table data cannot be empty

Solutions:

• Ensure the table data has at least one row
• Validate the data source (CSV, Excel, etc.) has data
• Check that data transformation didn't remove all rows

Duplicate Entry Error

Error: Duplicate entry 'value' for key 'PRIMARY' or 'unique_constraint'

Solutions:

• Enable the Replace option to update on duplicate
• Filter out duplicates before inserting
• Check if records already exist in the database
• Use a different unique value generator

Column Count Doesn't Match

Error: Column count doesn't match value count

Solutions:

• Ensure all rows have values for all columns
• Check that column names in table data match database columns exactly
• Verify case sensitivity (MySQL is case-sensitive on Linux)

Advanced Examples

ETL Pipeline

HTTP Request (fetch data from API)
  ↓
JavaScript (transform data)
  ↓
Query (check for existing records)
  ↓
JavaScript (filter new records)
  ↓
Insert Table (insert new records)

Incremental Load

// Check last import timestamp
// Query: SELECT MAX(ImportedAt) FROM target_table

const lastImport = $item.lastImportDate;

// Filter source data for new records
const newRecords = $item.sourceData.rows.filter(row => {
  return new Date(row.CreatedAt) > new Date(lastImport);
});

// Add import timestamp
const table = {
  columns: [...$item.sourceData.columns, 'ImportedAt'],
  rows: newRecords.map(row => ({
    ...row,
    ImportedAt: new Date().toISOString()
  }))
};

Upsert Pattern with Replace

Query (SELECT existing IDs)
  ↓
JavaScript (merge new and updated records)
  ↓
Insert Table (with Replace enabled)
  - Inserts new records
  - Updates existing records based on PRIMARY KEY

Multi-Table Insert with Transaction

Connect
  ↓
Start Transaction
  ↓
Insert Table (customers)
  ↓
Query (get new customer IDs)
  ↓
JavaScript (add customer IDs to orders data)
  ↓
Insert Table (orders)
  ↓
Commit
  ↓
Disconnect

Data Type Mapping

Ensure data types match MySQL column types:

JavaScript Type	MySQL Type	Example
String	VARCHAR, TEXT, CHAR	'John Doe'
Number	INT, BIGINT, DECIMAL, FLOAT	123, 45.67
Boolean	TINYINT(1), BOOLEAN	true → 1, false → 0
Date	DATE, DATETIME, TIMESTAMP	'2024-01-15', '2024-01-15 10:30:00'
null	NULL	null

Date Format

// Format dates for MySQL
const table = {
  columns: ["OrderDate", "ShipDate"],
  rows: $item.orders.map(order => ({
    OrderDate: new Date(order.orderDate).toISOString().slice(0, 10), // '2024-01-15'
    ShipDate: new Date(order.shipDate).toISOString().slice(0, 19).replace('T', ' ') // '2024-01-15 10:30:00'
  }))
};

See Also

• Connect - Establish database connections
• Query - Execute SELECT queries
• Non Query - Execute INSERT, UPDATE, DELETE
• Start Transaction - Begin transactions
• Commit - Commit transactions