mssql_bulk_insert
Insert multiple rows into Microsoft SQL Server tables efficiently using bulk operations for improved performance in data migration and batch processing scenarios.
Instructions
Insert multiple rows efficiently using bulk operations
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| server | Yes | MSSQL Server hostname or IP address | |
| port | No | Port number (default: 1433) | |
| user | Yes | Username for authentication | |
| password | Yes | Password for authentication | |
| database | Yes | Database name | |
| table | Yes | Target table name | |
| data | Yes | Array of objects representing rows to insert | |
| encrypt | No | Use encrypted connection (default: true) | |
| trustServerCertificate | No | Trust server certificate (default: true) |
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);