It's probably been about 3 years since I've had to crack open SQL Management Studio but this past week, I've been in SQL-land for hours at a time.
I'm rather fond of SQL. I have a few great memories of when I, as a BA, could school a developer or two with my magic.
Once was to build a dataset used to create a forced-direction network diagram of people who were connected to each other by being on the same email threads, regardless of whether they were recipients or senders. It was pretty neat because the solution was so simple: A UNION query with a self-referencing join.
That was my favorite win but my first time came about when I was listening to a developer complain about the tedious task of creating SQL statements to do some other tedious task. Copy id from Excel, paste into SQL statement, run, copy id from Excel, paste into SQL statement, run.
"Why are you doing it like that," I asked?
"How else am I going to do it?" He retorted.
"Use Excel."
Bam! It was so simple, I think he felt kind of silly afterwards but grateful, nonetheless.
I love using Excel for building text strings. I feel like I use it all the time as I did just the other day.
I have a list of IDs that I need to query against. If I was in my own personal database, or in Access, I could create a temp table and populate it with the IDs and do a join but, alas, I am a simple product manager and wouldn't dream of creating any tables in databases that aren't my own. What to do instead?
Build a SQL statement with an array for the WHERE clause. Fastest way to do that? Excel.
Sometimes I like to nerd out.
-Ann
I'm rather fond of SQL. I have a few great memories of when I, as a BA, could school a developer or two with my magic.
Once was to build a dataset used to create a forced-direction network diagram of people who were connected to each other by being on the same email threads, regardless of whether they were recipients or senders. It was pretty neat because the solution was so simple: A UNION query with a self-referencing join.
That was my favorite win but my first time came about when I was listening to a developer complain about the tedious task of creating SQL statements to do some other tedious task. Copy id from Excel, paste into SQL statement, run, copy id from Excel, paste into SQL statement, run.
"Why are you doing it like that," I asked?
"How else am I going to do it?" He retorted.
"Use Excel."
- I showed him how to create his SQL statement by building a string with an Excel formula and the ID column he was copying from.
- Copy the formula down for all rows.
- Paste into SQL
- Run all the lines at the same time.
Bam! It was so simple, I think he felt kind of silly afterwards but grateful, nonetheless.
I love using Excel for building text strings. I feel like I use it all the time as I did just the other day.
I have a list of IDs that I need to query against. If I was in my own personal database, or in Access, I could create a temp table and populate it with the IDs and do a join but, alas, I am a simple product manager and wouldn't dream of creating any tables in databases that aren't my own. What to do instead?
Build a SQL statement with an array for the WHERE clause. Fastest way to do that? Excel.
- Start with your list of IDs.
- Add a column for your SQL Helper.
- In this column, in the second row (or first row of data), put in a reference to this row's ID column. In my example '=A2'.
- In the third row of the same column, you'll concatenate the second row with the third row, joined by a comma using the formula '=B2 & ", " & A3'.
- Copy the formula from the third row all the way down your list. Be careful not to copy the second row because that's just going to give you a copy of your first column.
- Copy the last cell in your column and that's your array with all the IDs you need to query.
- SELECT * FROM table where ID in (<paste array here>)
Sometimes I like to nerd out.
-Ann
No comments:
Post a Comment