monochromagic posted:

For ETL stuff I'd recommend looking into dbt. There are many similar tools out there, and some might be better for your use case, but start with dbt. It is by far the largest and most well-supported, and seems to fit your needs, and you can run it open source. Also hooks nicely into CI/CD processes so you don't actually have to do the changes manually.

(As an aside - maybe also look into doing ELT instead, it's what all the cool kids are doing)

monochromagic posted:

Lib and let die posted:

I will occasionally tap into ChatGPT when I need my statements to bridge the gap between "clever" and "fancy", and did so this morning when working with some data exported from salesforce (my goal was to take each Account with a type of 'Household' from the Accounts file and populate, in-line, the primary household member (indicated in the one2oneContact field as per usual SF) and all associated members of the household identified by the [AccountId] in the Contacts file - the insert script for this particular item in our database is built around the expectation that we are getting data in the form of [HouseholdID][Member1][Member2][Member3] and so on - suffice to say it needs some serious reworking but that's neither here nor there).

Anyway, chatGPT gave me a fine output. It might not be the most efficient way to do it, but it gives me the results I want. I've got to export the results to excel and do a text to columns then re-import it, but it's still way less annoying than it could be.

My question, ultimately, is with ChatGPT's followup 'notes' or whatever.

What exactly is the "STUFF and FOR XML PATH('') technique" and what is it doing?

I can provide the full output here, but the relevant code bit is this:

code:

SELECT
        pc.AccountID,
        STUFF((SELECT ', ' + CAST(sc.ContactID AS VARCHAR(500))
               FROM SecondaryContacts sc
               WHERE pc.AccountID = sc.AccountID
               FOR XML PATH('')), 1, 2, '') AS SecondaryContactIDs
    FROM PrimaryContacts pc

pc and sc are aliases assigned to CTEs for primary and secondary contacts defined a bit further up in the output, for reference

Lib and let die posted:

And there's the problem with ChatGPT: I can use STRING_AGG lmfao

GigaPeon posted:

Here's what I was looking at

SQL Prompt: https://www.red-gate.com/products/sql-prompt/ People at the summit I was talking to were big on this one. Saying it saved them 100s of hours. Refactoring and templates and macros. Let's you define or pick a house style. Of course now they're using ChatGPT integration for some stuff. "Make this sp more efficient". Talking to a Redgate guy at an afterparty, he said that they're planning on making it so you can limit training to your own data for security concerns. I've been using their online formatter for a long time (https://www.red-gate.com/website/sql-formatter) so I was interested in the more robust features of this