CREATE TABLE dbo.Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
ContactName NVARCHAR(100),
Country NVARCHAR(50),
AddressLine1 NVARCHAR(200),
AddressLine2 NVARCHAR(200),
City NVARCHAR(100),
State NVARCHAR(100),
PostalCode NVARCHAR(20)
);
Prompt:
Remove the Address information from the above table into a separate
Addresses table with the Customer Id as foreign key.
Output:
-- Modified Customers Table:
CREATE TABLE dbo.Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
ContactName NVARCHAR(100),
Country NVARCHAR(50)
);
-- New Addresses Table:
CREATE TABLE dbo.Addresses (
AddressID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
AddressLine1 NVARCHAR(200),
AddressLine2 NVARCHAR(200),
City NVARCHAR(100),
State NVARCHAR(100),
PostalCode NVARCHAR(20),
FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)
);
Prompt:
Create a Countries table that can be referenced from the Addresses
table with Country ISO codes.
Output:
--Create the Countries Table:
CREATE TABLE dbo.Countries (
CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
CountryName NVARCHAR(100) NOT NULL
);
--Modify the Addresses Table:
CREATE TABLE dbo.Addresses (
AddressID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
AddressLine1 NVARCHAR(200),
AddressLine2 NVARCHAR(200),
City NVARCHAR(100),
State NVARCHAR(100),
PostalCode NVARCHAR(20),
CountryCode CHAR(2),
FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID),
FOREIGN KEY (CountryCode) REFERENCES dbo.Countries(CountryCode)
);
Prompt:
Extend the Countries table with ISO 3 characters code and ISO Number.
Output:
CREATE TABLE dbo.Countries (
CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
CountryName NVARCHAR(100) NOT NULL,
ISO3Code CHAR(3), -- ISO 3166-1 alpha-3 code
ISONumericCode INT -- ISO 3166-1 numeric code
);
Prompt:
Create a Regions table with Region code and description that allows
to group the countries in territories. A country can be assigned to
multiple regions.
Output:
-- Create the Regions Table:
CREATE TABLE dbo.Regions (
RegionCode CHAR(3) PRIMARY KEY,
RegionDescription NVARCHAR(100) NOT NULL
);
-- Create the Countries Table (including the previous columns):
CREATE TABLE dbo.Countries (
CountryCode CHAR(2) PRIMARY KEY, -- ISO 3166-1 alpha-2 code
CountryName NVARCHAR(100) NOT NULL,
ISO3Code CHAR(3), -- ISO 3166-1 alpha-3 code
ISONumericCode INT -- ISO 3166-1 numeric code
);
-- Create the CountryRegions Junction Table:
CREATE TABLE dbo.CountryRegions (
CountryCode CHAR(2),
RegionCode CHAR(3),
PRIMARY KEY (CountryCode, RegionCode),
FOREIGN KEY (CountryCode) REFERENCES dbo.Countries(CountryCode),
FOREIGN KEY (RegionCode) REFERENCES dbo.Regions(RegionCode)
);
Prompt:
Create a view based on Customers that includes all the above tables.
Output:
CREATE VIEW CustomerDetails AS
SELECT
c.CustomerID,
c.CustomerName,
c.ContactName,
a.AddressLine1,
a.AddressLine2,
a.City,
a.State,
a.PostalCode,
co.CountryCode,
co.CountryName,
co.ISO3Code,
co.ISONumericCode,
r.RegionCode,
r.RegionDescription
FROM
dbo.Customers c
LEFT JOIN
dbo.Addresses a ON c.CustomerID = a.CustomerID
LEFT JOIN
dbo.Countries co ON a.CountryCode = co.CountryCode
LEFT JOIN
dbo.CountryRegions cr ON co.CountryCode = cr.CountryCode
LEFT JOIN
dbo.Regions r ON cr.RegionCode = r.RegionCode;
Prompt:
In the above view use 3 letter encoding for table's name.
Output:
CREATE VIEW CustomerDetails AS
SELECT
cus.CustomerID,
cus.CustomerName,
cus.ContactName,
add.AddressLine1,
add.AddressLine2,
add.City,
add.State,
add.PostalCode,
cou.CountryCode,
cou.CountryName,
cou.ISO3Code,
cou.ISONumericCode,
reg.RegionCode,
reg.RegionDescription
FROM
dbo.Customers cus
LEFT JOIN
dbo.Addresses add ON cus.CustomerID = add.CustomerID
LEFT JOIN
dbo.Countries cou ON add.CountryCode = cou.CountryCode
LEFT JOIN
dbo.CountryRegions cr ON cou.CountryCode = cr.CountryCode
LEFT JOIN
dbo.Regions reg ON cr.RegionCode = reg.RegionCode;
Unfortunately, the definition uses the "add" reserved word for addresses
as prefix. Therefore, the following prompt:
Prompt:
Modify the
above view to use 3-letter aliases that aren't as reserved words.
Output:
CREATE VIEW dbo.CustomerDetails AS
SELECT
cus.CustomerID, -- Customer ID
cus.CustomerName, -- Customer name
cus.ContactName, -- Contact name
adr.AddressLine1, -- First line of the address
adr.AddressLine2, -- Second line of the address
adr.City, -- City of the address
adr.State, -- State or region of the address
adr.PostalCode, -- Postal or ZIP code of the address
cou.CountryCode, -- Country code
cou.CountryName, -- Country name
cou.ISO3Code, -- ISO 3166-1 alpha-3 code
cou.ISONumericCode, -- ISO 3166-1 numeric code
reg.RegionCode, -- Region code
reg.RegionDescription -- Region description
FROM
dbo.Customers cus
LEFT JOIN
dbo.Addresses adr ON cus.CustomerID = adr.CustomerID
LEFT JOIN
dbo.Countries cou ON adr.CountryCode = cou.CountryCode
LEFT JOIN
dbo.CountryRegions cr ON cou.CountryCode = cr.CountryCode
LEFT JOIN
dbo.Regions reg ON cr.RegionCode = reg.RegionCode;
Prompt:Modify the schema of the above objects to include the schema name.
Prompt:
Change the definition of the above tables and test whether the
tables already exists.
Prompt:
Annotate the definition of the above objects with a comment.
Prompt:
Generate the scripts for inserting 10 customers with different
addresses in the above tables.
Prompt:
Aggregate the update and insert commands into the same stored
procedure.
Output:
CREATE PROCEDURE dbo.UpsertCustomer
@CustomerID INT = NULL, -- Optional parameter for insert
@CustomerName NVARCHAR(100),
@ContactName NVARCHAR(100),
@Country NVARCHAR(50)
AS
BEGIN
IF EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
BEGIN
-- Update existing customer
UPDATE dbo.Customers
SET
CustomerName = @CustomerName,
ContactName = @ContactName,
Country = @Country
WHERE
CustomerID = @CustomerID;
END
ELSE
BEGIN
-- Insert new customer
INSERT INTO dbo.Customers (CustomerName, ContactName, Country)
VALUES (@CustomerName, @ContactName, @Country);
END
END;
I used to create similar code in the past to consolidate the logic for
inserts and updates within the same stored procedure. This allows to
reduce the number of objects created in a database and minimize the impact
of some other issues (e.g. code duplication for validation).
I also use the comma before each line of text where it applies (and not at
the end). However, this can be addressed with the following prompt:
Prompt:
Rewrite the above database objects by butting the comma in front of
each line where it applies.
Prompt:
Modify the view so each join condition is written on its own line.
Prompt:
Modify the view with create or alter command.
Prompt:
Modify the view to check for null values.
Comments:
1) I could use the scripts also in a SQL database, though one
must pay attention to the order in which the steps must be run.
2)
All these are basic steps; so, a natural question: how far can we go? One
can generate further objects, and knowledge about the data model seems to
be available in Copilot, though normal data models are much more complex
than this! Probably, there's a limit beyond which Copilot will start to be
inefficient as long the data model is not available explicitly. SQL Server
Copilot would help probably to overcome such limitations, though the
feature is still in limited preview.
3) I wonder whether given a set
of best practices, Copilot will be able to use them and create the CRUD
objects accordingly. More experiments are needed though.
4) It will
be interesting to observe how much the prompts generated by other people
lead to similar or different outcomes. (I observed that nontechnical
prompts generated in the past different outcomes.)
5) The fact that I
was able to change some formatting (e.g. comma before each line) for all
objects with just a prompt frankly made my day! I can't stress enough how
many hours of work the unformatted code required in the past! It will be
interesting to check if this can be applied also to a complex database
schema.
Happy coding!
Previous Post
<<||>>
Next Post
References:
[1] Microsoft 365 (2024) Overview of prompts [link]
Resources:
[R1] Microsoft 365 (2025) Microsoft 365 Copilot release notes
[link]
[R2] Microsoft 365 (2025) What’s new in Microsoft 365 Copilot |
Jan 2025 [link]
[R3] Microsoft 365 (2025) Copilot is now included in Microsoft 365
Personal and Family [link]
Acronyms:
LLM - large language model
CRUD - Create, Read, Update, Delete
