I was trying to do a little testing of our subsetter and needed a larger database. So I decided to ask Prompt + to help me with a little AI assist. This post looks at what happened.
This is part of a series of experiments with AI systems.
Getting More Data
I had a Northwind_FullRestore from my experiments with Alex Yate’s quick PoC tool. I selected that database and asked for a little code. I asked the AI to give me 1000 more rows of data in the Customers table. I got this code.
-- This script inserts 1000 rows data entry into the dbo.Customers table
WITH numbers
AS ( SELECT TOP (1000)
ROW_NUMBER () OVER (ORDER BY
(SELECT NULL)) AS num
FROM sys.all_objects)
--INSERT INTO dbo.Customers
-- (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
SELECT
'CUST' + RIGHT('0000' + CAST (num AS nchar(4)), 5)
, 'Customer' + CAST (num AS nvarchar)
, 'Contact' + CAST (num AS nvarchar)
, 'Title' + CAST (num AS nvarchar)
, 'Address' + CAST (num AS nvarchar)
, 'City' + CAST (num AS nvarchar)
, 'Region' + CAST (num AS nvarchar)
, 'PostalCode' + CAST (num AS nvarchar)
, 'Country' + CAST (num AS nvarchar)
, 'Phone' + CAST (num AS nvarchar)
, 'Fax' + CAST (num AS nvarchar)
FROM numbers;
Then I decided to go big. I changed my prompt, as you see below, and got more data. My new prompt: expand this to add 10GB of data to this table
I got this code:
-- This script adds approximately 10GB of data to the dbo.Customers table
-- Firstly, we need to calculate the approximate size of each row.
-- For simplicity, we'll assume each row is ~1KB in size after considering nvarchar data expansion.
-- Therefore, to add 10GB of data, we need approximately 10,000,000 rows.
WITH numbers
AS ( SELECT TOP (10000) -- This will generate 10,000 rows in the CTE
ROW_NUMBER () OVER (ORDER BY
(SELECT NULL)) AS num
FROM
sys.all_objects a
, sys.all_objects b)
INSERT INTO dbo.Customers
(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
SELECT
'CUST' + RIGHT('00000000' + CAST (numbers.num + seqnum AS nvarchar), 8) -- 8 character CustomerID to accommodate large number of rows
, 'Customer' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Contact' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Title' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Address' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'City' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Region' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'PostalCode' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Country' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Phone' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
, 'Fax' + CAST (numbers.num AS nvarchar) + CAST (seqnum AS nvarchar)
FROM
numbers
CROSS JOIN
( SELECT TOP (1000)
ROW_NUMBER () OVER (ORDER BY
(SELECT NULL)) AS seqnum
FROM sys.all_objects) seq;
The Results
The results were a bit underwhelming. I ran it and got this:
The problem here is that the CustomerID is a 5 character field. The comment in the code notes that this is now an 8 character field, but that’s not great as I might not want to change the size of the field.
I then asked this: change this script to ensure the customerID is less than 5 characters and alphanumeric
I got the same script, but the first line of the select was:
'CUST' + RIGHT('00000000' + CAST (numbers.num + seqnum AS nvarchar), 8)
-- 8 character CustomerID to accommodate large number of rows
Now when I run it, I get a problem with postal code, which now has the truncation error.
My Thoughts
It’s easy to say this was a failed experiment. I didn’t get working code. But I got a bunch of code that was close to what I need, in a fraction of the time that it would take me to write this, even with SQL Prompt. Then add in the fact that I can edit this code to what I need, which works, and saves me times.
I think this has potential for shortcutting some work and getting me closer to what I need quickly, even if it’s not perfect. If I’d have asked a junior dev to help me with this, I might still have to edit their code. Just as I do with my AI assistant.
Voice of the DBA 