Loading 80,000 Excel Records into SQL Server Using Microsoft Copilot

Introduction

Recently, I faced a practical challenge: loading approximately 80,000 records from an Excel file into a SQL Server database within a tight deadline.

Instead of manually writing SQL INSERT statements, I explored how Microsoft Copilot could help automate the process.

In this article, I'll share the challenge I faced, how I used Copilot, the prompt that worked, and how I completed the task efficiently.

  1. The Challenge

I received an Excel file containing approximately 80,000 records.

The requirements were:

  • Load all records into a SQL Server table.
  • The data contained 17 distinct values in the "Type" column.
  • Group and sort records based on "Type".
  • Each INSERT batch must contain a maximum of 1,000 records.
  • Generate executable SQL scripts.
  • Validate the scripts in the UAT environment.

Initially, I asked Microsoft Copilot to generate INSERT statements for all 80,000 records in one request.

However, the request exceeded Copilot's processing and response limits.

I then realised that the problem needed to be divided into smaller, manageable tasks.

  1. My Solution Using Microsoft Copilot

Instead of asking Copilot to process the entire Excel file at once, I provided clear instructions with specific technical constraints.

My approach was:

  1. Read the Excel data.
  2. Identify the 17 unique values in the "Type" column.
  3. Group and sort records by "Type".
  4. Divide the records into batches of 1,000.
  5. Generate a separate SQL INSERT script for each batch.
  6. Save the generated scripts as individual SQL files.

This resulted in approximately 80 batch scripts.

  1. The Prompt That Worked

Here is the structured prompt I used with Microsoft Copilot:

Prompt:

I have an Excel file containing approximately 80,000 records that need to be inserted into a SQL Server table.

Please follow these requirements:

  1. Identify all unique values in the "Type" column.
  2. Group and sort the records by "Type".
  3. Split the records into batches of 1,000.
  4. Generate a separate SQL Server INSERT script for each batch.
  5. Ensure that no script contains more than 1,000 rows.
  6. Use SQL Server-compatible INSERT syntax.
  7. Generate one batch at a time instead of processing all records in one response.
  8. Provide each completed batch as a downloadable SQL file.

Start with the first batch of 1,000 records.

  1. SQL INSERT Example

Instead of generating 1,000 individual INSERT statements, I used a multi-row INSERT approach.

Example:

INSERT INTO dbo.TargetTable
(
Column1,
Column2,
Type
)
VALUES
(
'Value1',
'Value2',
'TypeA'
),
(
'Value3',
'Value4',
'TypeA'
);

SQL Server supports a maximum of 1,000 row-value expressions in a single "INSERT ... VALUES" statement.

Therefore, dividing the records into batches helped meet this limitation.

  1. UAT Validation

Generating SQL scripts is only one part of the task. Validating the data is equally important.

I executed the scripts batch by batch in the UAT environment and verified:

  • Total number of inserted records.
  • Record distribution across all 17 types.
  • NULL values and data formatting.
  • Duplicate records and primary-key constraints.
  • Successful execution of every batch.

This helped identify potential issues before proceeding with the next batch.

  1. Results

The batch-based approach helped me:

  • Prepare approximately 80 SQL scripts.
  • Handle all 80,000 records.
  • Complete script generation and UAT validation in approximately one hour.
  • Maintain better control over data validation and execution.

The activity was completed successfully within the planned timeline.

  1. Key Learnings

My biggest learning was that the way we communicate with AI tools makes a significant difference.

Initially, I gave Copilot a large task and expected it to process everything in one go.

When I broke the requirement into smaller steps and clearly defined the constraints, the results improved significantly.

A few important takeaways:

  • Break large tasks into smaller, manageable steps.
  • Provide clear and specific prompts.
  • Define technical constraints before generating code.
  • Always validate AI-generated output.
  • Use AI as an engineering assistant, not as a replacement for technical judgment.

Conclusion

AI tools like Microsoft Copilot can be valuable assistants in everyday software engineering activities.

We don't always need complex AI solutions to improve productivity. Sometimes, identifying a repetitive task and using AI with the right instructions can make a meaningful difference.

The key is not just knowing how to use AI, but knowing how to give AI the right problem to solve.