Justin Cooney

MS Copilot (formerly known as Bing Chat with GPT-4) can be useful not just for generating funny images and random trivia, but for actual, useful code snippets. I’ve been playing around with this to see what it can do and what kind of prompts it takes to get the code that I want.

For this example I’m interested in getting Copilot to create a tSQL code snippet that will create a temporary table variable and then insert a list of ten countries into it along with their latest annual GDP.

Although the question seems fairly straightforward to me, getting the code results I wanted out of Copilot took some trial and error including it getting stuck in a loop where it wouldn’t remove code that I told it I didn’t want.

From my experience here, it sems that Copilot encourages iteratively refining answers based on an initial prompt. However it does not seem very good at revising its initial premise and I eventually ended up having to revise my initial prompt in a new session.

What Didn’t Work

My initial prompt was:

write a sql server temporary table variable and add in the top ten countries and their gdp

In response Copilot generated the following code:

DECLARE @temp_table TABLE (

    country VARCHAR(50),

    gdp FLOAT

);
INSERT INTO @temp_table (country, gdp)

SELECT TOP 10 country, gdp

FROM original_table

ORDER BY gdp DESC;

So overall, the code is ok, but not really what I was asking for. Copilot correctly generated the temporary table variable with the correct columns, but off the bat Copilot seems to assume that I want to select from a pre-existing table in a pretend database. What I in fact actually want is for Copilot to get a list of countries/GDP by itself and then enter this directly in the SQL. How to properly tell Copilot to do this?

I tried the prompt below to get Copilot to do an insert of countries directly. Copilot quickly answered back ‘Sure’ and then spat back the identical SQL as it initially had provided.

add into the temporary table ten countries and their gdp

I ended up trying to brute-force the issue by asking Copilot to directly add a country and its GDP, but this was not successful and Copilot went into a loop where I could not get it to remove selecting from some imaginary countries table (original_table).

In the end I had to restart my query and try explaining what I wanted in more detail from the original prompt. Finally this seemed to work and I got the SQL I was expecting to see.

What Worked

Here is the prompt that ended up generating the code I was expecting to see:

write a sql server temporary table variable and add in the top ten countries and their gdp. Insert the countries directly and do not assume that a table exists to select from

From this prompt I got the following SQL:

DECLARE @TopTenCountries TABLE (

    CountryName VARCHAR(50),

    GDP FLOAT

);



INSERT INTO @TopTenCountries (CountryName, GDP)

VALUES

    ('United States', 22.67),

    ('China', 16.16),

    ('Japan', 5.15),

    ('Germany', 4.17),

    ('India', 3.05),

    ('United Kingdom', 2.62),

    ('France', 2.58),

    ('Brazil', 2.05),

    ('Italy', 1.93),

    ('Canada', 1.65);



SELECT * FROM @TopTenCountries;

Take Away Thoughts

As you can see, Copilot does seem to generate code snippets quickly and accurately. Here are my take-away thoughts based on the simple code example above:

1. Copilot encourages refining the original results with further prompts from the user, assuming its original results are not incorrect.
2. If the initial results to your prompt are not correct then it may be easier to start the session from scratch rather than convincing Copilot to fix its results.
3. In your initial prompt to Copilot, it is best to be extra descriptive and know what copilot may do wrong in advance and write your prompt to discourage the incorrect results. You can only know this by seeing the wrong results first so it may be useful to make a mental note of past attempts with Copilot.

All in all, the snippets that Copilot writes are useful, especially if you know what you want. If you already know a language, then it may be faster to write your code yourself, but if you are not familiar with the language, then the code that Copilot writes may be useful to you.