Skip to main content

Execute Non Query

Executes a SQL Server command that does not return data (INSERT, UPDATE, DELETE, CREATE, ALTER, etc.).

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 command is part of a database transaction.
  • SQL Query - The SQL command to execute (INSERT, UPDATE, DELETE, etc.). Supports mustache template variables (e.g., {{userId}}).

Options​

  • Command Timeout - Command execution timeout in seconds (default: 30). Increase for long-running commands.
  • Credentials - SQL Server database credentials (optional). Use this if you want to execute the command without a Connect node.

Output​

  • None - The command is executed, but no results are returned.

How It Works​

The Execute Non Query node executes a SQL command that modifies data or database structure. 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. Processes mustache template variables in the SQL command using the message context
  4. Decodes any HTML entities in the rendered command
  5. Creates a SqlCommand with the SQL command and timeout settings
  6. Executes the command using ExecuteNonQuery
  7. Returns without output (number of affected rows is not returned)

Requirements​

  • Either: A valid connection ID from Connect node OR valid database credentials
  • Valid SQL command (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, etc.)
  • If using transactions, a valid transaction ID from Start Transaction node
  • Appropriate permissions (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, etc.)

Error Handling​

The node will return specific errors in the following cases:

  • ErrInvalidArg - Neither connection ID nor credentials provided, or command timeout is invalid
  • ErrNotFound - Connection ID not found in connections dictionary
  • ErrConnection - Cannot connect to SQL Server (when using credentials mode)
  • ErrRuntime - SQL command execution failed (syntax error, constraint violation, permission denied, etc.)

Usage Examples​

UPDATE Statement​

UPDATE Customers
SET Status = 'Inactive', LastModified = GETDATE()
WHERE CustomerID = {{customerId}}

DELETE Statement​

DELETE FROM OrderDetails
WHERE OrderID = {{orderId}}
  AND ProductID = {{productId}}

INSERT Statement​

INSERT INTO Customers (CustomerName, Email, City, Country, Status)
VALUES ('{{name}}', '{{email}}', '{{city}}', '{{country}}', 'Active')

Multiple Inserts​

INSERT INTO AuditLog (Action, UserID, Timestamp, Details)
VALUES ('{{action}}', {{userId}}, GETDATE(), '{{details}}')

CREATE TABLE​

CREATE TABLE TempResults (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  CustomerName NVARCHAR(100),
  TotalOrders INT,
  TotalRevenue DECIMAL(18,2),
  ProcessedDate DATETIME DEFAULT GETDATE()
)

ALTER TABLE​

ALTER TABLE Products
ADD LastUpdated DATETIME DEFAULT GETDATE(),
    UpdatedBy NVARCHAR(50)

DROP TABLE​

DROP TABLE IF EXISTS TempProcessingData

TRUNCATE TABLE​

TRUNCATE TABLE StagingData

UPDATE with JOIN​

UPDATE o
SET o.Status = 'Shipped', o.ShipDate = GETDATE()
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = '{{country}}'
  AND o.Status = 'Pending'
  AND o.OrderDate >= '{{startDate}}'

DELETE with JOIN​

DELETE od
FROM OrderDetails od
INNER JOIN Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate < DATEADD(year, -2, GETDATE())
  AND o.Status = 'Cancelled'

Common Use Cases​

  1. Data Updates - Update customer information, order status, inventory levels
  2. Data Deletion - Remove outdated records, clean up temporary data
  3. Data Insertion - Add new records, log events, create audit trails
  4. Table Management - Create temporary tables, alter schema, drop unused tables
  5. Batch Processing - Bulk updates, mass deletions, data cleanup
  6. Database Maintenance - Rebuild indexes, update statistics, shrink databases
  7. Schema Changes - Add columns, modify constraints, create indexes
  8. Data Migration - Transform and update data during migrations

Tips for Effective Use​

  • Template Variables - Use mustache syntax {{variable}} for dynamic values and to prevent SQL injection
  • Transaction Safety - Use transactions for multi-step data modifications
  • Backup First - Always backup data before bulk DELETE or UPDATE operations
  • Test with SELECT - Test WHERE clauses with SELECT before running UPDATE or DELETE
  • Affected Rows - If you need affected row count, query it separately or use OUTPUT clause
  • Command Timeout - Increase timeout for bulk operations that take longer
  • Direct Credentials - Use optional Credentials field for one-off commands
  • Error Handling - Wrap critical operations in Try-Catch blocks
  • Constraint Violations - Handle foreign key and unique constraint errors appropriately

Best Practices​

Safe UPDATE Pattern​

-- First, verify what will be updated
-- Run this in a Query node:
SELECT * FROM Customers
WHERE Status = 'Pending' AND CreatedDate < '{{cutoffDate}}'

-- Then execute the update
UPDATE Customers
SET Status = 'Expired', LastModified = GETDATE()
WHERE Status = 'Pending' AND CreatedDate < '{{cutoffDate}}'

Safe DELETE Pattern​

-- First, count what will be deleted
-- Run this in a Query node:
SELECT COUNT(*) AS RecordsToDelete
FROM TempData
WHERE ProcessedDate < DATEADD(day, -30, GETDATE())

-- Then execute the delete
DELETE FROM TempData
WHERE ProcessedDate < DATEADD(day, -30, GETDATE())

Conditional Execution​

// JavaScript node before Non Query
const recordsToUpdate = $item.countResult.rows[0].RecordCount;

if (recordsToUpdate > 0 && recordsToUpdate < 10000) {
  // Safe to proceed
  return $item;
} else {
  throw new Error(`Unexpected record count: ${recordsToUpdate}`);
}

Transaction Pattern​

Connect
  ↓
Start Transaction
  ↓
Non Query 1 (UPDATE orders)
  ↓
Non Query 2 (UPDATE inventory)
  ↓
Non Query 3 (INSERT audit log)
  ↓
Commit Transaction
  ↓
Disconnect

Advanced Examples​

UPDATE with OUTPUT Clause​

-- Create temp table to store updated values
CREATE TABLE #UpdatedRecords (
  CustomerID INT,
  OldStatus NVARCHAR(50),
  NewStatus NVARCHAR(50)
)

-- Update with OUTPUT
UPDATE Customers
SET Status = '{{newStatus}}'
OUTPUT
  inserted.CustomerID,
  deleted.Status AS OldStatus,
  inserted.Status AS NewStatus
INTO #UpdatedRecords
WHERE Status = '{{oldStatus}}'

-- Then query the temp table to get updated records
-- SELECT * FROM #UpdatedRecords

MERGE Statement (Upsert)​

MERGE INTO Customers AS target
USING (
  SELECT
    '{{customerId}}' AS CustomerID,
    '{{name}}' AS CustomerName,
    '{{email}}' AS Email
) AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED THEN
  UPDATE SET
    CustomerName = source.CustomerName,
    Email = source.Email,
    LastModified = GETDATE()
WHEN NOT MATCHED THEN
  INSERT (CustomerID, CustomerName, Email, CreatedDate)
  VALUES (source.CustomerID, source.CustomerName, source.Email, GETDATE());

Batch UPDATE with Chunking​

-- Update in batches to avoid locking
DECLARE @BatchSize INT = 1000
DECLARE @RowsAffected INT = @BatchSize

WHILE @RowsAffected = @BatchSize
BEGIN
  UPDATE TOP (@BatchSize) Products
  SET LastChecked = GETDATE()
  WHERE LastChecked IS NULL
    OR LastChecked < DATEADD(day, -7, GETDATE())

  SET @RowsAffected = @@ROWCOUNT
END

Conditional INSERT​

-- Insert only if record doesn't exist
IF NOT EXISTS (
  SELECT 1 FROM Customers
  WHERE Email = '{{email}}'
)
BEGIN
  INSERT INTO Customers (CustomerName, Email, CreatedDate)
  VALUES ('{{name}}', '{{email}}', GETDATE())
END

Dynamic SQL (Use with Caution)​

DECLARE @TableName NVARCHAR(128) = '{{tableName}}'
DECLARE @ColumnName NVARCHAR(128) = '{{columnName}}'
DECLARE @Value NVARCHAR(MAX) = '{{value}}'

-- Build dynamic SQL
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) +
           ' SET ' + QUOTENAME(@ColumnName) + ' = @Value' +
           ' WHERE ID = @ID'

-- Execute
EXEC sp_executesql @SQL,
  N'@Value NVARCHAR(MAX), @ID INT',
  @Value = @Value,
  @ID = {{recordId}}

Index Management​

CREATE INDEX​

CREATE NONCLUSTERED INDEX IX_Customers_Email
ON Customers (Email)
INCLUDE (CustomerName, City)
WHERE Status = 'Active'

DROP INDEX​

DROP INDEX IF EXISTS IX_Customers_OldColumn ON Customers

REBUILD INDEX​

ALTER INDEX ALL ON Orders REBUILD
WITH (ONLINE = ON, FILLFACTOR = 90)

UPDATE STATISTICS​

UPDATE STATISTICS Customers WITH FULLSCAN

Database Maintenance​

DBCC Commands​

-- Check database integrity
DBCC CHECKDB ('{{databaseName}}') WITH NO_INFOMSGS

-- Shrink database (use sparingly)
DBCC SHRINKDATABASE ('{{databaseName}}', 10)

-- Update statistics
EXEC sp_updatestats

Backup Database​

BACKUP DATABASE [{{databaseName}}]
TO DISK = '{{backupPath}}'
WITH FORMAT, INIT, NAME = 'Full Backup', COMPRESSION

Restore Database​

RESTORE DATABASE [{{databaseName}}]
FROM DISK = '{{backupPath}}'
WITH REPLACE, RECOVERY

Common Errors and Solutions​

SQL Command Execution Failed​

Error: ErrRuntime: SQL command execution failed

Common Causes:

  • Syntax error in SQL
  • Table or column doesn't exist
  • Constraint violation (foreign key, unique, check)
  • Permission denied
  • Deadlock or timeout
  • Invalid template variable values

Solutions:

  • Test command in SQL Server Management Studio (SSMS) first
  • Verify table and column names
  • Check constraint definitions
  • Ensure user has appropriate permissions (UPDATE, DELETE, etc.)
  • Use transactions to handle deadlocks
  • Increase Command Timeout for long operations
  • Validate template variables before execution

Constraint Violation​

Error: The INSERT statement conflicted with the FOREIGN KEY constraint

Solutions:

  • Verify referenced records exist in parent table
  • Check foreign key relationships
  • Insert parent records before child records
  • Use transactions to ensure data consistency

Error: Violation of UNIQUE KEY constraint

Solutions:

  • Check for duplicate values
  • Query existing records before INSERT
  • Use MERGE for upsert operations
  • Handle duplicates with appropriate logic

Permission Denied​

Error: The INSERT/UPDATE/DELETE permission was denied on the object

Solutions:

  • Grant appropriate permissions to the database user
  • Check table-level permissions
  • Verify schema-level permissions
  • Use a user with sufficient privileges

Command Timeout Expired​

Error: Timeout expired. The timeout period elapsed prior to completion

Solutions:

  • Increase the Command Timeout option
  • Optimize the command (add indexes, reduce rows affected)
  • Add WHERE clause to limit rows processed
  • Split large operations into batches
  • Check for table locks or blocking queries

Deadlock Victim​

Error: Transaction was deadlocked and chosen as the deadlock victim

Solutions:

  • Implement retry logic in your automation
  • Use transactions consistently
  • Access tables in the same order across all operations
  • Reduce transaction scope and duration
  • Consider using READ COMMITTED SNAPSHOT isolation

Performance Optimization​

Batch Operations​

-- Instead of multiple single updates
-- Use batch update with IN clause or JOIN

UPDATE Customers
SET Status = 'VIP'
WHERE CustomerID IN ({{customerIdList}})

-- Or use temporary table
CREATE TABLE #CustomerUpdates (CustomerID INT, NewStatus NVARCHAR(50))
INSERT INTO #CustomerUpdates VALUES
  ({{id1}}, '{{status1}}'),
  ({{id2}}, '{{status2}}')

UPDATE c
SET c.Status = u.NewStatus
FROM Customers c
INNER JOIN #CustomerUpdates u ON c.CustomerID = u.CustomerID

Minimize Logging​

-- For bulk deletes, consider truncate if appropriate
TRUNCATE TABLE StagingData  -- Faster than DELETE, but can't use WHERE

-- For large deletes, batch them
DELETE TOP (10000) FROM LargeTable
WHERE Status = 'Obsolete'

Use Appropriate Isolation Level​

-- For non-critical updates, use lower isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

UPDATE Statistics
SET LastCalculated = GETDATE()
WHERE CalculationType = '{{type}}'

Data Validation​

Pre-Execution Validation​

// JavaScript node before Non Query
const userId = $item.userId;
const newStatus = $item.newStatus;

// Validate inputs
if (!userId || userId <= 0) {
  throw new Error('Invalid user ID');
}

if (!['Active', 'Inactive', 'Suspended'].includes(newStatus)) {
  throw new Error('Invalid status value');
}

// Continue to Non Query
return $item;

Post-Execution Verification​

Non Query (UPDATE records)
  ↓
Query (SELECT updated records to verify)
  ↓
JavaScript (validate update was successful)

Using Direct Credentials (Without Connect)​

Non Query Node:
  - SQL Query: UPDATE Products SET Stock = Stock - 1 WHERE ProductID = {{pid}}
  - Credentials: [Select Database Credential]
  - Connection Id: (leave empty)

This creates a temporary connection, executes the command, and closes the connection automatically.

See Also​