insert_data
Add single or multiple records to MSSQL database tables using SQL INSERT statements with proper column mapping and data type validation.
Instructions
Inserts data into an MSSQL Database table. Supports both single record insertion and multiple record insertion using standard SQL INSERT with VALUES clause. FORMAT EXAMPLES: Single Record Insert: { "tableName": "Users", "data": { "name": "John Doe", "email": "john@example.com", "age": 30, "isActive": true, "createdDate": "2023-01-15" } } Multiple Records Insert: { "tableName": "Users", "data": [ { "name": "John Doe", "email": "john@example.com", "age": 30, "isActive": true, "createdDate": "2023-01-15" }, { "name": "Jane Smith", "email": "jane@example.com", "age": 25, "isActive": false, "createdDate": "2023-01-16" } ] } GENERATED SQL FORMAT:
Single: INSERT INTO table (col1, col2) VALUES (@param1, @param2)
Multiple: INSERT INTO table (col1, col2) VALUES (@param1, @param2), (@param3, @param4), ... IMPORTANT RULES:
For single record: Use a single object for the 'data' field
For multiple records: Use an array of objects for the 'data' field
All objects in array must have identical column names
Column names must match the actual database table columns exactly
Values should match the expected data types (string, number, boolean, date)
Use proper date format for date columns (YYYY-MM-DD or ISO format)
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| data | Yes | ||
| tableName | Yes | Name of the table to insert data into |
Implementation Reference
- src/tools/InsertDataTool.ts:72-140 (handler)The main handler function `run` that executes the insert_data tool logic, handling both single and multiple record insertions into an MSSQL table using parameterized queries.async run(params: any) { try { const { tableName, data } = params; // Check if data is an array (multiple records) or single object const isMultipleRecords = Array.isArray(data); const records = isMultipleRecords ? data : [data]; if (records.length === 0) { return { success: false, message: "No data provided for insertion", }; } // Validate that all records have the same columns const firstRecordColumns = Object.keys(records[0]).sort(); for (let i = 1; i < records.length; i++) { const currentColumns = Object.keys(records[i]).sort(); if (JSON.stringify(firstRecordColumns) !== JSON.stringify(currentColumns)) { return { success: false, message: `Column mismatch: Record ${i + 1} has different columns than the first record. Expected columns: [${firstRecordColumns.join(', ')}], but got: [${currentColumns.join(', ')}]`, }; } } const columns = firstRecordColumns.join(", "); const request = new sql.Request(); if (isMultipleRecords) { // Multiple records insert using VALUES clause - works for 1 or more records const valueClauses: string[] = []; records.forEach((record, recordIndex) => { const valueParams = firstRecordColumns .map((column, columnIndex) => `@value${recordIndex}_${columnIndex}`) .join(", "); valueClauses.push(`(${valueParams})`); // Add parameters for this record firstRecordColumns.forEach((column, columnIndex) => { request.input(`value${recordIndex}_${columnIndex}`, record[column]); }); }); const query = `INSERT INTO ${tableName} (${columns}) VALUES ${valueClauses.join(", ")}`; await request.query(query); return { success: true, message: `Successfully inserted ${records.length} record${records.length > 1 ? 's' : ''} into ${tableName}`, recordsInserted: records.length, }; } else { // Single record insert (when data is passed as single object) const values = firstRecordColumns .map((column, index) => `@value${index}`) .join(", "); firstRecordColumns.forEach((column, index) => { request.input(`value${index}`, records[0][column]); }); const query = `INSERT INTO ${tableName} (${columns}) VALUES (${values})`; await request.query(query); return { success: true, message: `Successfully inserted 1 record into ${tableName}`, recordsInserted: 1, }; } } catch (error) { console.error("Error inserting data:", error); return { success: false, message: `Failed to insert data: ${error}`, }; } }
- src/tools/InsertDataTool.ts:49-71 (schema)Input schema definition for the insert_data tool, specifying tableName and data (either single object or array of objects).inputSchema = { type: "object", properties: { tableName: { type: "string", description: "Name of the table to insert data into" }, data: { oneOf: [ { type: "object", description: "Single record data object with column names as keys and values as the data to insert. Example: {\"name\": \"John\", \"age\": 30}" }, { type: "array", items: { type: "object" }, description: "Array of data objects for multiple record insertion. Each object must have identical column structure. Example: [{\"name\": \"John\", \"age\": 30}, {\"name\": \"Jane\", \"age\": 25}]" } ] }, }, required: ["tableName", "data"], } as any;
- src/index.ts:90-90 (registration)Instantiation of the InsertDataTool instance.const insertDataTool = new InsertDataTool();
- src/index.ts:115-119 (registration)Registration of the insert_data tool in the list of available tools returned by ListToolsRequestSchema handler.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: isReadOnly ? [listTableTool, readDataTool, describeTableTool] // todo: add searchDataTool to the list of tools available in readonly mode once implemented : [insertDataTool, readDataTool, describeTableTool, updateDataTool, createTableTool, createIndexTool, dropTableTool, listTableTool], // add all new tools here }));
- src/index.ts:126-127 (registration)Dispatch case in CallToolRequestSchema handler that routes calls to insert_data to the tool's run method.case insertDataTool.name: result = await insertDataTool.run(args);