Reformat an INSERT INTO...VALUES Statement with ChatGPT

Prompt Engineering

With recent advancements in AI, the ability to craft good prompts will be an increasingly important developer skill.  Prompt fu is the new Google fu.

INSERT INTO statements can be notoriously difficult to troubleshoot because the fields and values are defined on different lines.

For example, I asked ChatGPT to, "Create a sample T-SQL INSERT INTO ... VALUES statement with at least 10 fields using a common scenario from a desktop line of business application.  Do not include comments."

INSERT INTO dbo.Customers
    (CustomerID, FirstName, LastName, Email, Phone, Address, City, State, ZipCode, MembershipLevel)
VALUES
    (1001, 'John', 'Doe', 'john.doe@example.com', '(555) 123-4567', '123 Main St', 'Anytown', 'CA', '90210', 'Gold'),
    (1002, 'Jane', 'Smith', 'jane.smith@example.com', '(555) 987-6543', '456 Oak Ave', 'Sometown', 'NY', '10001', 'Silver'),
    (1003, 'Mike', 'Johnson', 'mike.johnson@example.com', '(555) 555-5555', '789 Elm Rd', 'Othertown', 'TX', '75001', 'Bronze'),
    (1004, 'Sarah', 'Williams', 'sarah.williams@example.com', '(555) 222-3333', '101 Pine Ln', 'Cityville', 'FL', '32003', 'Gold');

Debugging a statement like the one above is challenging because you have to count commas to figure out which values go with which fields.  But what if you didn't have to?

ChatGPT Prompt

Use the following prompt to transform a SQL statement like the one above into something much easier to troubleshoot:

Rewrite the following T-SQL statement to include a comma-separated list of all values being assigned to each field as an inline comment with each field on its own line.  Also, include the field associated with each value as inline comments with each value on its own line:

{ Paste T-SQL }

For example:
INSERT INTO TableName (
   Field1, -- Field1_Value1, Field1_Value2, Field1_Value3, etc.
   Field2, -- Field2_Value1, Field2_Value2, Field2_Value3, etc.
   ...
VALUES (
   Field1_Value1,  -- Field1
   Field2_Value1, -- Field2
   ...

Sample Usage

PROMPT

Rewrite the following T-SQL statement to include a comma-separated list of all values being assigned to each field as an inline comment with each field on its own line.  Also, include the field associated with each value as inline comments with each value on its own line:

INSERT INTO dbo.Customers
    (CustomerID, FirstName, LastName, Email, Phone, Address, City, State, ZipCode, MembershipLevel)
VALUES
    (1001, 'John', 'Doe', 'john.doe@example.com', '(555) 123-4567', '123 Main St', 'Anytown', 'CA', '90210', 'Gold'),
    (1002, 'Jane', 'Smith', 'jane.smith@example.com', '(555) 987-6543', '456 Oak Ave', 'Sometown', 'NY', '10001', 'Silver'),
    (1003, 'Mike', 'Johnson', 'mike.johnson@example.com', '(555) 555-5555', '789 Elm Rd', 'Othertown', 'TX', '75001', 'Bronze'),
    (1004, 'Sarah', 'Williams', 'sarah.williams@example.com', '(555) 222-3333', '101 Pine Ln', 'Cityville', 'FL', '32003', 'Gold');

For example:

INSERT INTO TableName
    (
        Field1, -- Field1_Value1, Field1_Value2, Field1_Value3, etc.
        Field2, -- Field2_Value1, Field2_Value2, Field2_Value3, etc.
...
VALUES
  ( Field1_Value1,  -- Field1
    Field2_Value1, -- Field2
...

RESPONSE

Sure, here's the rewritten T-SQL statement with comma-separated lists of all values being assigned to each field as inline comments with each field on its own line:

INSERT INTO dbo.Customers
    (
        CustomerID, -- 1001, 1002, 1003, 1004
        FirstName, -- 'John', 'Jane', 'Mike', 'Sarah'
        LastName, -- 'Doe', 'Smith', 'Johnson', 'Williams'
        Email, -- 'john.doe@example.com', 'jane.smith@example.com', 'mike.johnson@example.com', 'sarah.williams@example.com'
        Phone, -- '(555) 123-4567', '(555) 987-6543', '(555) 555-5555', '(555) 222-3333'
        Address, -- '123 Main St', '456 Oak Ave', '789 Elm Rd', '101 Pine Ln'
        City, -- 'Anytown', 'Sometown', 'Othertown', 'Cityville'
        State, -- 'CA', 'NY', 'TX', 'FL'
        ZipCode, -- '90210', '10001', '75001', '32003'
        MembershipLevel -- 'Gold', 'Silver', 'Bronze', 'Gold'
    )
VALUES
    (
        1001, -- CustomerID
        'John', -- FirstName
        'Doe', -- LastName
        'john.doe@example.com', -- Email
        '(555) 123-4567', -- Phone
        '123 Main St', -- Address
        'Anytown', -- City
        'CA', -- State
        '90210', -- ZipCode
        'Gold' -- MembershipLevel
    ),
    (
        1002, -- CustomerID
        'Jane', -- FirstName
        'Smith', -- LastName
        'jane.smith@example.com', -- Email
        '(555) 987-6543', -- Phone
        '456 Oak Ave', -- Address
        'Sometown', -- City
        'NY', -- State
        '10001', -- ZipCode
        'Silver' -- MembershipLevel
    ),
    (
        1003, -- CustomerID
        'Mike', -- FirstName
        'Johnson', -- LastName
        'mike.johnson@example.com', -- Email
        '(555) 555-5555', -- Phone
        '789 Elm Rd', -- Address
        'Othertown', -- City
        'TX', -- State
        '75001', -- ZipCode
        'Bronze' -- MembershipLevel
    ),
    (
        1004, -- CustomerID
        'Sarah', -- FirstName
        'Williams', -- LastName
        'sarah.williams@example.com', -- Email
        '(555) 222-3333', -- Phone
        '101 Pine Ln', -- Address
        'Cityville', -- City
        'FL', -- State
        '32003', -- ZipCode
        'Gold' -- MembershipLevel
    );

In this revised statement, each field has a corresponding inline comment that lists all the values assigned to that field as comma-separated lists. Similarly, each value has a corresponding inline comment that mentions the field it belongs to. This makes it easier to understand which values are assigned to which fields in the INSERT INTO statement.

Acknowledgements

• One or more code samples generated with the help of ChatGPT
• Sample data generated with the help of ChatGPT
• Robot teacher image generated by DALL-E via Microsoft Designer