mssql_bulk_insert

Instructions

Implementation Reference

• src/index.ts:846-947 (handler)

  The main handler function that executes the mssql_bulk_insert tool logic. It validates input, checks table structure, validates data columns, performs batched INSERT operations using parameterized queries, and returns insertion statistics.

  private async handleBulkInsert(args: any) {
    const config = ConnectionSchema.parse(args);
    const { table, data } = args;
  
    if (!Array.isArray(data) || data.length === 0) {
      throw new MCPError('Data must be a non-empty array', 'INVALID_DATA');
    }
  
    try {
      const pool = await this.getConnection(config);
      
      // Get table structure to validate data
      const structureRequest = pool.request();
      const structureResult = await structureRequest.query(`
        USE [${config.database}];
        SELECT 
          COLUMN_NAME,
          DATA_TYPE,
          IS_NULLABLE,
          COLUMN_DEFAULT
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = '${table}'
        ORDER BY ORDINAL_POSITION
      `);
  
      if (structureResult.recordset.length === 0) {
        throw new MCPError(`Table '${table}' not found`, 'TABLE_NOT_FOUND');
      }
  
      const columns = structureResult.recordset;
      const columnNames = columns.map(col => col.COLUMN_NAME);
  
      // Validate data structure
      const firstRow = data[0];
      const dataColumns = Object.keys(firstRow);
      const invalidColumns = dataColumns.filter(col => !columnNames.includes(col));
      
      if (invalidColumns.length > 0) {
        throw new MCPError(
          `Invalid columns: ${invalidColumns.join(', ')}. Valid columns: ${columnNames.join(', ')}`,
          'INVALID_COLUMNS'
        );
      }
  
      // Build bulk insert query
      const insertColumns = dataColumns.join(', ');
      const valuesPlaceholder = `(${dataColumns.map(() => '?').join(', ')})`;
      const allValues = data.map(row => dataColumns.map(col => row[col])).flat();
  
      // For large datasets, use batching
      const batchSize = 100;
      let totalInserted = 0;
      
      for (let i = 0; i < data.length; i += batchSize) {
        const batch = data.slice(i, i + batchSize);
        const batchValues = batch.map(row => dataColumns.map(col => row[col])).flat();
        const batchPlaceholders = batch.map(() => valuesPlaceholder).join(', ');
        
        const insertQuery = `
          USE [${config.database}];
          INSERT INTO [${table}] (${insertColumns})
          VALUES ${batchPlaceholders}
        `;
  
        const request = pool.request();
        // Add parameters
        batchValues.forEach((value, index) => {
          request.input(`param${index}`, value);
        });
  
        const finalQuery = insertQuery.replace(/\?/g, (match, offset) => {
          const paramIndex = insertQuery.substring(0, offset).split('?').length - 1;
          return `@param${paramIndex}`;
        });
  
        const result = await request.query(finalQuery);
        totalInserted += result.rowsAffected[0] || 0;
      }
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              server: config.server,
              database: config.database,
              table: table,
              totalRowsProcessed: data.length,
              totalRowsInserted: totalInserted,
              status: 'success'
            }, null, 2),
          },
        ],
      };
    } catch (error) {
      throw new MCPError(
        `Bulk insert failed: ${error instanceof Error ? error.message : String(error)}`,
        'BULK_INSERT_ERROR',
        { table, rowCount: data.length }
      );
    }
  }

• src/index.ts:411-425 (schema)

  The input schema defining parameters for the mssql_bulk_insert tool, including connection details, table name, data array, and optional connection flags.

  inputSchema: {
    type: 'object',
    properties: {
      server: { type: 'string', description: 'MSSQL Server hostname or IP address' },
      port: { type: 'number', description: 'Port number (default: 1433)', default: 1433 },
      user: { type: 'string', description: 'Username for authentication' },
      password: { type: 'string', description: 'Password for authentication' },
      database: { type: 'string', description: 'Database name' },
      table: { type: 'string', description: 'Target table name' },
      data: { type: 'array', description: 'Array of objects representing rows to insert' },
      encrypt: { type: 'boolean', description: 'Use encrypted connection (default: true)', default: true },
      trustServerCertificate: { type: 'boolean', description: 'Trust server certificate (default: true)', default: true },
    },
    required: ['server', 'user', 'password', 'database', 'table', 'data'],
  },

• src/index.ts:408-426 (registration)

  The tool registration object that defines the name, description, and input schema for mssql_bulk_insert, added to the tools array.

  {
    name: 'mssql_bulk_insert',
    description: 'Insert multiple rows efficiently using bulk operations',
    inputSchema: {
      type: 'object',
      properties: {
        server: { type: 'string', description: 'MSSQL Server hostname or IP address' },
        port: { type: 'number', description: 'Port number (default: 1433)', default: 1433 },
        user: { type: 'string', description: 'Username for authentication' },
        password: { type: 'string', description: 'Password for authentication' },
        database: { type: 'string', description: 'Database name' },
        table: { type: 'string', description: 'Target table name' },
        data: { type: 'array', description: 'Array of objects representing rows to insert' },
        encrypt: { type: 'boolean', description: 'Use encrypted connection (default: true)', default: true },
        trustServerCertificate: { type: 'boolean', description: 'Trust server certificate (default: true)', default: true },
      },
      required: ['server', 'user', 'password', 'database', 'table', 'data'],
    },
  },

• src/index.ts:453-454 (registration)

  The switch case in the tool request handler that routes calls to the mssql_bulk_insert tool to its handler function.

  case 'mssql_bulk_insert':
    return await this.handleBulkInsert(args);