MSSQL MCP Server

Instructions

Implementation Reference

• src/tools/InsertDataTool.ts:72-140 (handler)

  The main execution logic for the 'insert_data' tool. Handles 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 required 'tableName' and 'data' fields supporting 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:109-113 (registration)

  Registers 'insertDataTool' in the list of available tools for the ListToolsRequestHandler (non-readonly mode).

  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:120-121 (registration)

  Registers the handler call for 'insert_data' tool in the CallToolRequestHandler switch statement.

  case insertDataTool.name:
    result = await insertDataTool.run(args);

• src/index.ts:84-84 (registration)

  Instantiates the InsertDataTool instance used throughout the server.

  const insertDataTool = new InsertDataTool();