TInsertSqlStatement

Overview

The TInsertSqlStatement node represents INSERT statements in SQL. It handles various forms of data insertion including VALUES clauses, subqueries, and database-specific extensions.

SQL to AST Mapping

Simple INSERT with VALUES

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());

AST Structure:

TInsertSqlStatement
├── targetTable: TTable (Production.UnitMeasure)
├── columnList: TObjectNameList (Name, UnitMeasureCode, ModifiedDate)
├── insertSource: EInsertSource.values
└── values: TMultiTargetList
    └── TMultiTarget
        ├── TExpression (N'Square Yards')
        ├── TExpression (N'Y2')
        └── TExpression (GETDATE())

INSERT with Subquery

INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%';

AST Structure:

TInsertSqlStatement
├── targetTable: TTable (dbo.EmployeeSales)
├── insertSource: EInsertSource.subquery
└── subQuery: TSelectSqlStatement
    ├── resultColumnList: TResultColumnList
    ├── fromClause: TFromClause
    └── whereClause: TWhereClause

Multi-row INSERT

INSERT INTO users (name, email, age) VALUES 
('John Doe', 'john@example.com', 30),
('Jane Smith', 'jane@example.com', 25),
('Bob Johnson', 'bob@example.com', 35);

AST Structure:

TInsertSqlStatement
├── targetTable: TTable (users)
├── columnList: TObjectNameList (name, email, age)
├── insertSource: EInsertSource.values
└── values: TMultiTargetList
    ├── TMultiTarget (John Doe, john@example.com, 30)
    ├── TMultiTarget (Jane Smith, jane@example.com, 25)
    └── TMultiTarget (Bob Johnson, bob@example.com, 35)

Key Properties

Property	Type	Description	Access Method
targetTable	TTable	Target table for insertion	getTargetTable()
columnList	TObjectNameList	Column names (optional)	getColumnList()
insertSource	EInsertSource	Type of data source	getInsertSource()
values	TMultiTargetList	VALUES clause data	getValues()
subQuery	TSelectSqlStatement	Subquery data source	getSubQuery()
insertToken	TSourceToken	INSERT keyword	getInsertToken()

Insert Source Types

The EInsertSource enum determines the type of data source:

Source Type	Description	Access Method
values	VALUES clause	getValues()
subquery	SELECT statement	getSubQuery()
values_empty	Empty VALUES ()	N/A
default_values	DEFAULT VALUES	N/A
execute	EXECUTE statement (SQL Server)	getExecuteStmt()
values_function	Function call	getFunctionCall()

Common Usage Patterns

Analyzing Insert Data Sources

public void analyzeInsertStatement(TInsertSqlStatement insert) {
    System.out.println("Target table: " + insert.getTargetTable().getFullName());

    // Check column list
    if (insert.getColumnList() != null) {
        System.out.println("Columns specified:");
        for (int i = 0; i < insert.getColumnList().size(); i++) {
            System.out.println("  " + insert.getColumnList().getObjectName(i));
        }
    }

    // Analyze data source
    switch (insert.getInsertSource()) {
        case values:
            analyzeValuesClause(insert.getValues());
            break;
        case subquery:
            analyzeSubquery(insert.getSubQuery());
            break;
        case default_values:
            System.out.println("Using DEFAULT VALUES");
            break;
        default:
            System.out.println("Other insert source: " + insert.getInsertSource());
    }
}

Processing VALUES Clauses

public void analyzeValuesClause(TMultiTargetList values) {
    if (values == null) return;

    System.out.println("Number of value rows: " + values.size());

    for (int i = 0; i < values.size(); i++) {
        TMultiTarget row = values.getMultiTarget(i);
        System.out.println("Row " + (i + 1) + ":");

        if (row.getColumnList() != null) {
            for (int j = 0; j < row.getColumnList().size(); j++) {
                TResultColumn col = row.getColumnList().getResultColumn(j);
                TExpression expr = col.getExpr();
                System.out.println("  Value " + (j + 1) + ": " + expr.toString());
            }
        }
    }
}

Extracting Referenced Tables

public Set<String> getReferencedTables(TInsertSqlStatement insert) {
    Set<String> tables = new HashSet<>();

    // Target table
    tables.add(insert.getTargetTable().getFullName());

    // Tables from subquery
    if (insert.getInsertSource() == EInsertSource.subquery && 
        insert.getSubQuery() != null) {

        insert.getSubQuery().acceptChildren(new TParseTreeVisitor() {
            public void preVisit(TParseTreeNode node) {
                if (node instanceof TTable) {
                    TTable table = (TTable) node;
                    tables.add(table.getFullName());
                }
            }
        });
    }

    return tables;
}

Column-Value Validation

public List<String> validateColumnValueMatch(TInsertSqlStatement insert) {
    List<String> errors = new ArrayList<>();

    if (insert.getColumnList() == null || insert.getValues() == null) {
        return errors; // No validation needed
    }

    int expectedColumns = insert.getColumnList().size();

    for (int i = 0; i < insert.getValues().size(); i++) {
        TMultiTarget row = insert.getValues().getMultiTarget(i);
        if (row.getColumnList() != null) {
            int actualValues = row.getColumnList().size();
            if (actualValues != expectedColumns) {
                errors.add(String.format(
                    "Row %d: Expected %d values, got %d", 
                    i + 1, expectedColumns, actualValues));
            }
        }
    }

    return errors;
}

Database-Specific Features

SQL Server

-- OUTPUT clause
INSERT INTO target_table (col1, col2)
OUTPUT INSERTED.id, INSERTED.col1
VALUES (1, 'value');

-- TOP clause
INSERT TOP (10) INTO target_table
SELECT * FROM source_table;

Access via: - getOutputClause() - OUTPUT clause - getTopClause() - TOP clause

MySQL

-- ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name, email) 
VALUES (1, 'John', 'john@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- IGNORE keyword
INSERT IGNORE INTO users (name, email) 
VALUES ('John', 'john@example.com');

Access via: - getOnDuplicateKeyUpdate() - ON DUPLICATE KEY UPDATE clause - getIgnore() - IGNORE keyword - getPriority_delayed() - Priority keywords

Oracle

-- INSERT ALL
INSERT ALL
  INTO sales (prod_id, cust_id, time_id, amount)
  VALUES (product_id, customer_id, weekly_start_date, sales_sun)
  INTO sales (prod_id, cust_id, time_id, amount)
  VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
SELECT * FROM sales_input_table;

-- Error logging
INSERT INTO target_table
SELECT * FROM source_table
LOG ERRORS INTO error_table;

Access via: - isInsertAll() - INSERT ALL - isInsertFirst() - INSERT FIRST - getErrorLoggingClause() - Error logging

PostgreSQL

-- RETURNING clause
INSERT INTO users (name, email) 
VALUES ('John', 'john@example.com')
RETURNING id, created_at;

Access via: - getReturningClause() - RETURNING clause

Performance Considerations

Batch Insert Analysis

public void analyzeBatchInsert(TInsertSqlStatement insert) {
    if (insert.getInsertSource() == EInsertSource.values && 
        insert.getValues() != null) {

        int rowCount = insert.getValues().size();
        if (rowCount > 1000) {
            System.out.println("Warning: Large batch insert (" + rowCount + " rows)");
            System.out.println("Consider using bulk insert methods");
        }
    }
}

Index Impact Assessment

public void assessIndexImpact(TInsertSqlStatement insert) {
    // Analyze which columns are being inserted
    // Check for potential index maintenance overhead
    // Suggest optimization strategies
}

See Also

• TMultiTargetList - VALUES clause structure
• TSelectSqlStatement - Subquery inserts
• TTable - Target table references
• SQL Statements - Other statement types