I recently used M365 Copilot with SQL and I continue to be amazed with how helpful it is. I used it to help me write SQL statements. It’s ability to figure out context and provide me with what I need with less prompting is astonishing. I wanted to share what I did as just one example of how you can leverage AI with your own development. There are of course, many many great ways to use AI in development. Some directly using Visual Studio, and others like this that are indirect. Often times people have indirect problems they want to solve, and still want to use AI. Therefore, I wanted to show this one using M365 Copilot. The purpose is not for you to build this same application. Instead, understand how to use AI to help your own development process.
Background
Before, I can share how I used M365 Copilot with SQL, I need to explain the application I have and the data structure.
In my work, clients will send us problems that my team works to solve. We have people who are assigned to monitor the incoming requests and make sure they are assigned out properly to the rest of the team.
There are multiple shifts throughout each day, where a person is assigned this ‘triage’ role. This way, one person does not have to do it for the entire day.
We have other ‘shifts’ where we need people to be assigned a role on a certain date and time. Such as a code reviewer. Or a person to promote code, ect.
We have an Excel document to manage the assignments of these shifts.
Web Application
Instead of continuing to use an Excel document, I created a Web App to manage this schedule. This would allow use team member to enter in the dates they are not available to take a shift. It also allows the system to generate the schedule based on people’s availability, and send them email invites automatically.
I used AI to create the web app from a prompt of what I needed it to do. But that is not the part I want to focus on. The part I want to share is how I loaded data after I created the application.
The Data
The web application reads and writes to several different tables.
First, I have a table that stores all of the ‘team members’, named SchedTriageMembers.
Second, a table stores all of the available ‘shifts’ each day, named SchedShifts.
Third, a table stores which ‘team members’ are eligible to be assigned each ‘shift’. For example, I have some team members who live on the east coast. Therefore, I only want the system to assign them shifts that are in the morning. Similarly, I have people who live on the west coast. I only want them assigned shifts that are in the afternoon. Lastly, I have team members who live in India. These team members can be assigned the US ‘night shift’. This table is called SchedScheduleAssignments.
Fourth, I have table that stores all of the holiday’s for each country. US, and Canada, and India each have a different holiday calendar. This table is named SchedHolidays. I also have a table named SchedCountries that stores the countries.
Fifth, I have a table that stores when each team member is out of the office or otherwise unavailable to take a shift. This table is named ‘SchedMemberTimeOff‘.
Six, the team is large and therefore some team members work on one system, while others work on another. So each team member is actually part of a group that I call a ‘team’. The table that stores the list of teams is named SchedTeams.
Ultimately, the tables don’t matter that much. The purpose of this article is to show you how M365 Copilot with SQL can work on your tables as well.
The Scheduler
To help you visualize my specific example, below is an example scheduler in Excel with made up names.
In the first column, the spreadsheet lists each day that needs to be scheduled. Next, each column lists each ‘shift’. Additionally, at the top of each column, lists which team members are eligible for each shift.
Ultimately, the goal is to populate the Excel table with names so that each date and shift is assigned a team member who is eligible and available to work that shift.
Tell M365 About Your Table Structure
Now comes the neat stuff on using M365 Copilot with SQL.
First, I copied into M365 Copilot a sql script that explains my table structure to AI. This could be any a select statement, or any other kind of statement that allows Copilot to see the table names and fields:
I have these tables and data in a database: "
INSERT [dbo].[SchedCountries] ([CountryId], [Name], [CreatedAt]) VALUES (1, N'US', CAST(N'2026-08-24T19:56:03.5350868' AS DateTime2))
INSERT [dbo].[SchedHolidays] ([HolidayId], [CountryId], [HolidayDate], [Name]) VALUES (1, 1, CAST(N'2026-09-07' AS Date), N'Labor Day')
INSERT [dbo].[SchedTeams] ([TeamId], [Name], [CreatedAt]) VALUES (1, N'F&SC', CAST(N'2026-08-24T19:56:03.7514177' AS DateTime2))
INSERT [dbo].[SchedTriageMembers] ([MemberId], [FirstName], [LastName], [TeamId], [CountryId], [CreatedAt], [Email]) VALUES (1, N'Ava', N'Smith', 1, 1, CAST(N'2026-08-24T20:37:10.4069066' AS DateTime2), N'')
INSERT [dbo].[SchedShifts] ([ShiftId], [TeamId], [Name], [SortOrder], [StartTime], [EndTime]) VALUES (1, 1, N'Morning Triage 8 AM-10:30 AM MST', 1, N'08:00', N'10:30')
INSERT [dbo].[SchedScheduleAssignments] ([Id], [TeamId], [ShiftId], [AssignedDate], [MemberId], [UpdatedAt], [GraphEventId]) VALUES (1, 1, 1, CAST(N'2026-08-24' AS Date), 1, CAST(N'2026-08-24T20:42:55.6624445' AS DateTime2), NULL)
INSERT [dbo].[SchedShiftMembers] ([ShiftId], [MemberId]) VALUES (1, 1)
INSERT [dbo].[SchedMemberTimeOff] ([Id], [MemberId], [OffDate]) VALUES (1, 7, CAST(N'2026-09-21' AS Date))"In any other system, you have to provide your table structure and field names in a very well defined format. Maybe XML or JSON. But using AI, it is smart enough to understand my table schema using whatever format I have it is. This is amazing!
Insert Records Using Copilot
Now that I have told Copilot my table structure, I can it generate TSQL scripts to insert my data.
In this example, I have a list of names, and I need them inserted into my ‘team member’ table. The good news is I again can give this information to Copilot in whatever format works for me.
In this case, I can say to Copilot: “Now take this list of all of the team members (ignoring duplicates), and generate a TSQL script to insert all team members into the table SchedTriageMembers that are not already there. Use Work IQ knowledge, or read my email address book to look up the country and email address of each person. Set the createdAt column to right now”.
I can then copy and past a picture of the names from my excel file.
Copilot is smart enough to read the names, retrieve additional information from Work IQ, and generate TSQL code to insert the data into the table I specified. Notice, I didn’t even provide it with the column names. It figured it out from context.
Below is essentially the TSQL Copilot produced. I modified the script to use my made up names.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
/*
Team:
F&SC
Country names must match:
US
India
Duplicate detection:
1. Email, case-insensitive
2. FirstName + LastName, case-insensitive
*/
DECLARE @TeamId int;
SELECT @TeamId = TeamId
FROM dbo.SchedTeams
WHERE Name = N'F&SC';
IF @TeamId IS NULL
BEGIN
THROW 50001, 'The F&SC team was not found in dbo.SchedTeams.', 1;
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.SchedCountries
WHERE Name = N'US'
)
BEGIN
THROW 50002, 'The US country was not found in dbo.SchedCountries.', 1;
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.SchedCountries
WHERE Name = N'India'
)
BEGIN
THROW 50003, 'The India country was not found in dbo.SchedCountries.', 1;
END;
DECLARE @MembersToInsert table
(
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
CountryName nvarchar(100) NOT NULL,
Email nvarchar(320) NOT NULL
);
INSERT INTO @MembersToInsert
(
FirstName,
LastName,
CountryName,
Email
)
VALUES
(N'Will', N'Smith', N'India', N'Will.Smith@gmail.com'),
(N'Liam', N'Dallas', N'India', N'Joshi.dallas@gmail.com'),
(N'Lucas', N'Doe', N'US', N'Lucas.doe@gmail.com'),
/*
Preview the records that qualify for insertion.
This can be retained for deployment validation.
*/
SELECT
SourceMember.FirstName,
SourceMember.LastName,
@TeamId AS TeamId,
Country.CountryId,
SourceMember.CountryName,
SourceMember.Email
FROM @MembersToInsert AS SourceMember
INNER JOIN dbo.SchedCountries AS Country
ON Country.Name = SourceMember.CountryName
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.SchedTriageMembers AS ExistingMember
WHERE
LOWER(LTRIM(RTRIM(ExistingMember.Email))) =
LOWER(LTRIM(RTRIM(SourceMember.Email)))
OR
(
LOWER(LTRIM(RTRIM(ExistingMember.FirstName))) =
LOWER(LTRIM(RTRIM(SourceMember.FirstName)))
AND
LOWER(LTRIM(RTRIM(ExistingMember.LastName))) =
LOWER(LTRIM(RTRIM(SourceMember.LastName)))
)
)
ORDER BY
SourceMember.LastName,
SourceMember.FirstName;
INSERT INTO dbo.SchedTriageMembers
(
FirstName,
LastName,
TeamId,
CountryId,
CreatedAt,
Email
)
SELECT
SourceMember.FirstName,
SourceMember.LastName,
@TeamId,
Country.CountryId,
SYSUTCDATETIME(),
SourceMember.Email
FROM @MembersToInsert AS SourceMember
INNER JOIN dbo.SchedCountries AS Country
ON Country.Name = SourceMember.CountryName
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.SchedTriageMembers AS ExistingMember
WHERE
LOWER(LTRIM(RTRIM(ExistingMember.Email))) =
LOWER(LTRIM(RTRIM(SourceMember.Email)))
OR
(
LOWER(LTRIM(RTRIM(ExistingMember.FirstName))) =
LOWER(LTRIM(RTRIM(SourceMember.FirstName)))
AND
LOWER(LTRIM(RTRIM(ExistingMember.LastName))) =
LOWER(LTRIM(RTRIM(SourceMember.LastName)))
)
);
DECLARE @InsertedCount int = @@ROWCOUNT;
COMMIT TRANSACTION;
SELECT
@InsertedCount AS InsertedMemberCount;
/*
Final validation result.
*/
SELECT
Member.MemberId,
Member.FirstName,
Member.LastName,
Team.Name AS TeamName,
Country.Name AS CountryName,
Member.Email,
Member.CreatedAt
FROM dbo.SchedTriageMembers AS Member
INNER JOIN dbo.SchedTeams AS Team
ON Team.TeamId = Member.TeamId
INNER JOIN dbo.SchedCountries AS Country
ON Country.CountryId = Member.CountryId
WHERE EXISTS
(
SELECT 1
FROM @MembersToInsert AS SourceMember
WHERE
LOWER(LTRIM(RTRIM(SourceMember.Email))) =
LOWER(LTRIM(RTRIM(Member.Email)))
)
ORDER BY
Member.LastName,
Member.FirstName;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
THROW;
END CATCH;
GOFinally, provided this type of prompt to create team members and add them to each of the teams that are part of this scheduler.
Combing Data
Next, I wanted to combined two shifts into one. Instead of writing the code myself I used M365 Copilot with SQL to do the work for me. I told it the two shifts I wanted to combine, and let it provide the TSQL code for making the change.
This is the prompt I provided. Notice, my prompt is not all that specific. However, Copilot is still able to understand my intent and generate the correct script.
I want to combine these two shifts into one
'Morning Triage 8 am - 1pm MST' shift. "INSERT [dbo].[SchedShifts] ([ShiftId], [TeamId], [Name], [SortOrder], [StartTime], [EndTime]) VALUES (1, 1, N'Morning Triage 8 AM-10:30 AM MST', 1, N'08:00', N'10:30')
INSERT [dbo].[SchedShifts] ([ShiftId], [TeamId], [Name], [SortOrder], [StartTime], [EndTime]) VALUES (2, 1, N'10:30-1:00 MST', 2, N'10:30', N'13:00')"
Give me the tsql scripts to make that change.
Additionally, combine these two shifts:
"INSERT [dbo].[SchedShifts] ([ShiftId], [TeamId], [Name], [SortOrder], [StartTime], [EndTime]) VALUES (3, 1, N'Afternoon Triage 1:00-3:30 MST', 3, N'13:00', N'15:30')
INSERT [dbo].[SchedShifts] ([ShiftId], [TeamId], [Name], [SortOrder], [StartTime], [EndTime]) VALUES (4, 1, N'Evening 3:30 PM-6PM MST', 4, N'15:30', N'18:00')"
to one shift named 'Afternoon/Evening Triage 1pm - 6pm MST'This is the script it produced. Because I told Copilot the unique identifier information in my request, it knew enough to make the changes.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
------------------------------------------------------------
-- 1. Combine Shift 1 and Shift 2 into Shift 1
------------------------------------------------------------
UPDATE dbo.SchedShifts
SET
Name = N'Morning Triage 8 AM - 1 PM MST',
SortOrder = 1,
StartTime = N'08:00',
EndTime = N'13:00'
WHERE ShiftId = 1
AND TeamId = 1;
------------------------------------------------------------
-- 2. Move any assignments for Shift 2 to Shift 1
------------------------------------------------------------
UPDATE dbo.SchedScheduleAssignments
SET ShiftId = 1
WHERE ShiftId = 2
AND TeamId = 1;
------------------------------------------------------------
-- 3. Delete old Shift 2
------------------------------------------------------------
DELETE FROM dbo.SchedShifts
WHERE ShiftId = 2
AND TeamId = 1;
------------------------------------------------------------
-- 4. Combine Shift 3 and Shift 4 into Shift 3
------------------------------------------------------------
UPDATE dbo.SchedShifts
SET
Name = N'Afternoon/Evening Triage 1 PM - 6 PM MST',
SortOrder = 2,
StartTime = N'13:00',
EndTime = N'18:00'
WHERE ShiftId = 3
AND TeamId = 1;
------------------------------------------------------------
-- 5. Move any assignments for Shift 4 to Shift 3
------------------------------------------------------------
UPDATE dbo.SchedScheduleAssignments
SET ShiftId = 3
WHERE ShiftId = 4
AND TeamId = 1;
------------------------------------------------------------
-- 6. Delete old Shift 4
------------------------------------------------------------
DELETE FROM dbo.SchedShifts
WHERE ShiftId = 4
AND TeamId = 1;
------------------------------------------------------------
-- 7. Re-sequence the remaining F&SC shifts
------------------------------------------------------------
UPDATE dbo.SchedShifts
SET SortOrder =
CASE ShiftId
WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 5 THEN 3
WHEN 6 THEN 4
WHEN 7 THEN 5
WHEN 8 THEN 6
END
WHERE TeamId = 1
AND ShiftId IN (1, 3, 5, 6, 7, 8);
COMMIT TRANSACTION;
------------------------------------------------------------
-- Validation
------------------------------------------------------------
SELECT
ShiftId,
TeamId,
Name,
SortOrder,
StartTime,
EndTime
FROM dbo.SchedShifts
WHERE TeamId = 1
ORDER BY SortOrder;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
GOAdvanced Inserts
Next, in my example, I want to assign team members who are eligible to work each shift using the data I already have in Excel. Typically this would be a lot of manual work to write insert statements for many different records. I also would need to look up the proper identifiers for each team member and shift.
Instead, M365 Copilot with SQL makes this very easy. It understands what I am trying to do with very little context and generates what I need.
Give me the tsql script to assign these people (ignore duplicates) to the 'Morning triage' shift. "Will, Liam, Lucas, Olivia, Emma"Impressively, Copilot generated the correct SQL code, even adding additional validation checks.
/*
Assign Morning Triage members
ShiftId 1 = Morning Triage 8 AM - 1 PM MST
*/
DECLARE @ShiftId int = 1;
-- Validate the shift exists
IF NOT EXISTS
(
SELECT 1
FROM dbo.SchedShifts
WHERE ShiftId = @ShiftId
AND TeamId = 1
)
BEGIN
THROW 50001, 'Morning Triage ShiftId 1 was not found.', 1;
END;
DECLARE @Members table
(
FirstName nvarchar(100),
LastName nvarchar(100)
);
INSERT INTO @Members
(
FirstName,
LastName
)
VALUES
(N'Will', N'Smith'),
(N'Liam', N'Dallas'),
(N'Lucas', N'Doe'),
(N'Olivia', N'Danger'),
(N'Emma', N'Marlow');
-- Insert only members not already assigned to this shift
INSERT INTO dbo.SchedShiftMembers
(
ShiftId,
MemberId
)
SELECT
@ShiftId,
tm.MemberId
FROM @Members m
INNER JOIN dbo.SchedTriageMembers tm
ON tm.FirstName = m.FirstName
AND tm.LastName = m.LastName
AND tm.TeamId = 1
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.SchedShiftMembers sm
WHERE sm.ShiftId = @ShiftId
AND sm.MemberId = tm.MemberId
);
-- Validate the resulting assignments
SELECT
s.ShiftId,
s.Name AS ShiftName,
tm.MemberId,
tm.FirstName,
tm.LastName,
tm.Email
FROM dbo.SchedShiftMembers sm
INNER JOIN dbo.SchedShifts s
ON s.ShiftId = sm.ShiftId
INNER JOIN dbo.SchedTriageMembers tm
ON tm.MemberId = sm.MemberId
WHERE sm.ShiftId = @ShiftId
ORDER BY
tm.LastName,
tm.FirstName;
GOAfter that, I repeated the prompts for each of the shifts. However, I probably could have just taken a screenshot of the list of names, along with the shift, and saved even more time.
Conclusion
Microsoft M365 Copilot with SQL can be very effective even with little context. This is a very random example, but the purpose is not to have you recreate this application. But to apply this same type of process to your own scenarios. Simply provide Copilot with context about the table schema you are working with. Then, provide it any context it needs to know about your data. Finally, ask it for what you need, providing it the data and instructions in whatever format you already have it in. Of course there are limitations, but I think you will be surprised by how good AI has become at understanding context, and discerning your intent. By practicing these skills you will safe time in many ways that were never possible before.
Leave a Reply