Tag: Redgate

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.